microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Charging for Successful Notifications - Extending the NS pipeline -

Subject: Re: Charging for Successful Notifications - Extending the NS pipeline - long(ish) post
From: "TonyS"
Date: Sun, 31 Dec 2006 11:09:40 -0000
Newsgroups: microsoft.public.sqlserver.notificationsvcs
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 
table/view.

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 
problem.

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 
it.

Thanks a lot in advance.

Tony S.

"Joe Webb" <[email protected]> wrote in message 
news:[email protected]
> 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.
>
> HTH...
>
> Joe
>
> -- 
> Joe Webb
> SQL Server MVP
> http://www.sqlns.com
>
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
>
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
>
> 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 
>> for
>> successful notifications) to poll the
>> NS<NotificationClassName>NotificationDistribution view periodically? Or 
>> is
>> there a more natural place in the workflow pipeline for this kind of
>> process?
>> TIA
>>
>> Tony S.
> 



<Prev in Thread] Current Thread [Next in Thread>