locked
Using just stored procedures to create and delete users RRS feed

  • Question

  • Is it safe to use the follow stored procedures in the order below to effect a bulk enrollment of users:
    1) NSMain.dbo.NSInsertSubscriber
    2) NSMain.dbo.NSInsertSubscriberDevice
    3) NSMain.dbo.NSInsert<SubClassName>SubscriptionSchedule
    4) NSMain.dbo.NSInsert<SubClassName>Subscription

    Is it safe to use the following stored procedures in the order below to effect a bulk delete of users:
    1) NSMain.dbo.NSDeleteAllSubscriptions
    2) NSMain.dbo.NSDeleteSubscriberDevice
    3) NSMain.dbo.NSDeleteSubscriber

    Please let me know.

    Thanks.
    Monday, July 11, 2005 6:48 PM

Answers

  • It is recommended that you use the Notification Services API found under the Microsoft.SqlServer.NotificationServices namespace.

    The classes you're looking for are:
    Subscriber
    SubscriberDevice
    Subscription

    Using the stored procedures you mention isn't recommended largely because they may change in future versions and cause application compatibility issues for you down the road.

    Thanks,
    Anand
    Tuesday, July 12, 2005 4:02 PM
  • Are you using SQL Server 2000 or 2005? In 2005, we've added a set of views that you can insert into directly, to submit subscriber, subscriber device, and subscription data. For example:

    -- Add a subscriber and a subscriber device
    INSERT INTO [NSInstance].[NSSubscriberView](SubscriberId, Enabled)
    VALUES (N'Bob', 1)

    INSERT INTO [NSInstance].[NSSubscriberDeviceView](SubscriberId, Enabled, DeviceName, DeviceTypeName, DeviceAddress, DeliveryChannelName)
    VALUES (N'Bob', 1, N'DefaultDevice', N'File', N'', N'FileChannel')

    These are available in the SQL 2005 June CTP and later.

    Hope this helps.
    -shyam

    Tuesday, July 26, 2005 8:19 PM

All replies

  • It is recommended that you use the Notification Services API found under the Microsoft.SqlServer.NotificationServices namespace.

    The classes you're looking for are:
    Subscriber
    SubscriberDevice
    Subscription

    Using the stored procedures you mention isn't recommended largely because they may change in future versions and cause application compatibility issues for you down the road.

    Thanks,
    Anand
    Tuesday, July 12, 2005 4:02 PM
  • I've developed a C# application that I use to create test subscribers,
    devices, and subscriptions for the SQLNS apps that I develop. It's
    written so that it works with all instances and applications on the
    local machine.

    If you're interested, it's available for free on the downloads page of
    my web site. http://www.webbtechsolutions.com.

     

    HTH...
    Joe Webb
    SQL Server MVP

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

     

    Tuesday, July 12, 2005 6:10 PM
  • Are you using SQL Server 2000 or 2005? In 2005, we've added a set of views that you can insert into directly, to submit subscriber, subscriber device, and subscription data. For example:

    -- Add a subscriber and a subscriber device
    INSERT INTO [NSInstance].[NSSubscriberView](SubscriberId, Enabled)
    VALUES (N'Bob', 1)

    INSERT INTO [NSInstance].[NSSubscriberDeviceView](SubscriberId, Enabled, DeviceName, DeviceTypeName, DeviceAddress, DeliveryChannelName)
    VALUES (N'Bob', 1, N'DefaultDevice', N'File', N'', N'FileChannel')

    These are available in the SQL 2005 June CTP and later.

    Hope this helps.
    -shyam

    Tuesday, July 26, 2005 8:19 PM