Joe, Thanks for your response.
After your post I considered and implemented a custom delivery channel based
on a stored proc that updates a table. This works quite well. I was glad to
get something like this working because it opens up a whole new world. The
stored proc can send messages to any system that can be expressed from the
stored proc. i.e. service broker, message queues, web services especially if
the stored procs are written using the CLR.
However, I am looking for a solution that is independent of any particular
protocol (i.e. that will work irrespective of which protocol was used to
deliver the message to the channel). To use the custom delivery channel it
has to be referenced by a specific protocol and therefore the subscriptions
have to specifically use a device that uses that protocol. Ideally I want to
calculate a charge for messages sent via any/all regular delivery channels
across any/all protocols rather than 'replacing' an standard existing
channel with a custom delivery channel.
The issue is that I want to inject a piece of functionality 'into the
pipeline' (so to speak) just after the message has been delivered to the
channel but I don't want to put a trigger on the notification distribution
So here is my question. Is it legitimate to treat the rows arriving in the
notification distribution view as new events and using an SQL event
provider, pick up (and filter out duplicates) rows from that distribution
view, map them to an 'internal/special' subscriber (with a device that
points to the channel) created beforehand + a special subscription that
causes a notification to be generated that uses the custom delivery channel
to perform the calculations? Phew?
If this makes sense, it provides a way to effectively extend the NS
pipeline. Hopefully using this method, the vacuumer would remove messages
before they were picked up as events, thereby eliminating a potential
If there is an easier way to achieve performing post-delivery actions
without resorting to a technique such as that described above I'd appreciate
Thanks a lot in advance.
"Joe Webb" <[email protected]> wrote in message
> The vacuumer will come around periodically and clear out the distribution
> tables so if you're going to rely on this method, you'll probably want to
> archive the info off to another table periodically.
> Another possibility for you may be to create a custom delivery channel
> that can write to a table to indicate the new charges for the subscriber.
> Joe Webb
> SQL Server MVP
> Get up to speed quickly with SQLNS
> I support PASS, the Professional Association for SQL Server.
> On Fri, 22 Dec 2006 18:46:12 +0000, TonyS wrote:
>> Is the best way to update a costings table (so that users can be charged
>> successful notifications) to poll the
>> NS<NotificationClassName>NotificationDistribution view periodically? Or
>> there a more natural place in the workflow pipeline for this kind of
>> Tony S.