sql server notification services: Hi,

I've a requirement to create a Single Use Subsciption, a
subscription that is created and once it has been used to
create a notification is removed.

I've been looking through the NS documentation, and cannot
find anything about such Subscriptions. So I've been
trying to figure out the most effective way of crow
barring the NS to support single use subscription.

So far I have thought of the following different ways:

1. I use the vacuum abilities of the SQL Server to clean
out Subscriptions that have a flag set agasint them.

2. The software which displays the Notification, will
delete a Subscription when the message is read.

Unfortunatley, I'm not happy with either method. So I was
wondering if anybody else has had experience with a Single
Use Subcription and how they implemented it.

Suggestions?

Thanks,

[quoted text, click to view]

Well, this may be crazy or dangerous, but what about appending a
"delete" to the subscription's rule? I think a delete against the
"subscriptions" view that's available to you should get the row from
the base table.

It seems that once the notification is generated, it should be safe to
do that. I'm scared to test it, though.

Cheers

Hi John -

In the currentversion of SQLNS there isn't an automatic way to send
one-off notifications. You can still do it; you'll just have to write it
yourself - much as you've described in your original post.

I believe the only supported/recommended way from MS to do this is
through the exposed API in the Microsoft.SqlServer.NotificationServices
namespace. You can try accessing the tables directly with all of the
normal warnings - doing so can cause unpredictable results, may be
broken in future releases, etc.

A couple of thoughts off the top of my head for you to consider:

If you're using a scheduled subscription that sends notifications once
per day, you can schedule a process to periodically review and disable
the appropriate subscriptions in the app database.

A custom delivery channel could disable/delete the subscription after
sending the notification.

Possibly more...


HTH...
Joe Webb

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811




[quoted text, click to view]