sql server notification services: I'm trying my best to avoid doing any direct access to the NS tables,
but I'm really finding it difficult to maintain that practice.

The particular problem I'm having right now is that I can't seem to
gracefully update a field in a subscription from classic ASP using COM
interop. The following is a snippet from an ASP Sub that tries to
update the devicename for a particular subscription, given that
subscription ID:

--------------------------------------
Set oInstance=SQLNSGetInstance()
Set oApplication=CreateObject("Microsoft.SqlServer.NotificationServices.NSApplication")
oApplication.Initialize oInstance, "MyApp"

Set oSubscription=CreateObject("Microsoft.SqlServer.NotificationServices.Subscription")
oSubscription.Initialize oApplication,"SubPostNotify"

oSubscription.SubscriptionID=szSubID

oSubscription.SetFieldValue "DeviceName","email"
oSubscription.Update
---------------------------------------

The problem is that setting the subscriptionID doesn't seem to
populate the subscription object properly. The .Update method fails
with the error that one of the other fields is blank.

Further testing shows that, immediately after setting .SubscriptionID
to a valid, existing subscriptionID, the .SubscriberID property isn't
set, and neither are any of the fields. In fact, if I do a
..GetFieldValue on any of the subscription fields (right after setting
the SubscriptionID property), the object throws a "the subscription
object has not been initialized" error.

Am I missing some key step where I need to tell the Subscription
object that it needs to seek the subscription ID I've populated it
with? If so, what's that secret? And if not, what am I missing?

Thanks

Brooks,
If you want to modify an existing subscription, you need to obtain the
subscription object from the subscription enumeration, rather than create a
new object and set the subscription ID.

Here's how to go about this:
1. Create a new SubscriberEnumeration object for the instance (see the
SubscriberEnumeration class documentation for details)
2. Look up the appropriate Subscriber in the SubscriberEnumeration based on
SubscriberId.
3. Get a SubscriptionEnumeration for that subscriber's subscriptions by
calling the GetSubscriptions() method on the Subscriber object.
4. Look up the appropriate Subcription object in the SubscriptionEnumeration
based on SubscriptionId

Then you will be able to modify the fields and call Update().

Hope this helps.
-shyam

--
Learn more about SQL-NS:
http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/
---------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
---------------------------------------------
[quoted text, click to view]
oApplication=CreateObject("Microsoft.SqlServer.NotificationServices.NSApplic
ation")
[quoted text, click to view]
oSubscription=CreateObject("Microsoft.SqlServer.NotificationServices.Subscri
ption")
[quoted text, click to view]