sql server notification services: Charging for Successful Notifications -- sql server notification services

Re: Charging for Successful Notifications

Joe Webb
12/26/2006 11:51:07 AM

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)

[quoted text, click to view]

Re: Charging for Successful Notifications - Extending the NS pipeline - long(ish) post

TonyS
12/31/2006 11:09:40 AM

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.

[quoted text, click to view]

Re: Charging for Successful Notifications - Extending the NS pipeline - long(ish) post

TonyS
1/6/2007 1:40:15 PM

Hi,
I wanted to respond to this mail so that others may benefit.
Since making this post I implemented my idea and I'd like to share what I
achieved and also say that it works really well.

To restate my problem: I wanted to be able to apply a cost to every
notification sent regardless of which delivery channel was used to send the
notification.

Joe kindly suggested creating a custom delivery protocol. The problem with
that solution is that a custom delivery channel replaces the existing
delivery channels. In other words the custom channel routes the outgoing
traffic through the custom channel instead of passing the notification on to
the existing channel. I wanted to send notifications and do something after
they left the channel, i.e. apply a charge to the subscriber.

In order to be able to optionally charge for the notification, based on
differing criteria, I needed to collect the delivery status information
after the notification passed through the existing channel.

For those interested here are the 10 things I did.
1) I created another application within the same instance and created
another ADF file.
2) I created an event class that was based on information recorded in the
NS<NotificationClassName>NotificationDistribution view.
3) I created a SQL Event Provider that read records from the view and passed
that information in as events.
4) I created a single internal subscriber for billing purposes
5) I create a single subscriber device that specified the stored procedure
delivery channel
6) I created a a single subscription (for billing purposes also) for the
subscriber specifying the device name.
7) I made sure that the notification class specified the stored procedure
delivery protocol.
8) The notification class needed no formatting so used the noop.xslt
9) Modified the config file to include the stored procedure that would be
called by the protocol
10) Wrote the stored procedure specified in the config file to write the
data to a history table I also created. (I will later extend the sp to check
for price offers etc but it is worth noting that instead of storing that
information I could have written in to a message queue or service broker
queue)

So whenever any event get submitted that goes successfully through the
system, the new application treats the arrival of the 'Delivery Succeeded'
message in the distribution view as an event in itself and sends it through
the stored procedure customer delivery channel to the history table.

I appreciate that it might have been simpler to use a separate SQL job to
poll the NotificationDistribution view but I wanted an all-in-one system.

Of course I'd have preferred a post delivery mechanism provided by SQLNS but
at least I learned a lot.

Tony S.

[quoted text, click to view]

Re: Charging for Successful Notifications - Extending the NS pipeline - long(ish) post

Elad Gov-Ari
1/17/2007 6:33:50 AM

Hi,
I was wondering if you could send me the stored procedure customed
delivery channel you have created.
I am facing the same issue and any help would be appreceated.

Re: Charging for Successful Notifications - Extending the NS pipeline - long(ish) post

TonyS
1/17/2007 8:14:25 PM

As requested, sent via email

Tony S.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server notification services group.