locked
Multiple Devices per subscription and SQL-NS API communication RRS feed

  • Question

  • Hi,

    I've got two questions, both targeting SQL-NS on SQL Server 2005.

    1.  What is the best way to handle a subscription that could have more than one device associated with it?  For example, a subscription that should result in both an Email and a phone call (via custom delivery channel).  I'm attempting to avoid needing to have two separate subscriptions that are identical other than device type.  This could be accomplished with a child table, but what's the best method of incorporating this into SQL-NS?

    2.  How does the SQL-NS API communicate with the SQL Server?  Is it simply a regular SQL connection over port 1433?

    Thanks,
    Peter

    Friday, August 12, 2005 9:44 PM

Answers

All replies

  • Hi,

    1: The best way to support this scenario is by have separate subscriptions per device. While this may increase your space requirements, it does give the subscriber greater controller over their subscriptions and how they want to receive their notifications.

    2: Yes the SQL-NS API uses a regular SQL connection over port 1433.

    Thanks,
    Anand
    Friday, August 19, 2005 3:42 AM
  • Depending on your requirements, you could investigate creating a custom delivery protocol that actually sends notifications via more than one protocol - for example, an email and an SMS notification. However this is almost certainly a bad idea. For instance, think about retry attempts after failures or partial failures.

    I'd encourage you to look more into the seperate subscription for each device. You can even manage it behind the scenes for your subscribers. The subscription management app could simply ask for one or more ways to contact them and then it can create the necessary subscriptions.


    --
    Joe Webb
    SQL Server MVP


    ~~~
    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)

     

    Friday, August 19, 2005 6:44 PM
  • Above you said:
    I'd encourage you to look more into the seperate subscription for each device. You can even manage it behind the scenes for your subscribers. The subscription management app could simply ask for one or more ways to contact them and then it can create the necessary subscriptions.


    How do you handle keeping them in sync then if the database connection was to go away after you update the first subscription? Without some sort of transaction control this could become a very LARGE headache to maintain.   
    Friday, August 19, 2005 7:54 PM
  • Hi -

    Here's a copy of a posting I recently made in the newsgroup....HTH...

    If you're using 2005, there are some new views that allow you to create subscribers, devices, and subscription data without going through the API.  So you could create a connection to SQL Server and issue something like:

    INSERT INTO NSInstance.NSSubscriberView (SubscriberId, Enabled)
    VALUES (N'joew@webbtechsolutions.com', 1)

    You could create a transaction object to handle that aspect of it.

    If you're using SQL Server NS 2000, then your options are more limited. You could use the API to create the the subscriber, device, and subscriptions and at the conclusion check to make sure everything was created appropriately. If not back out. 

    This scenario doesn't handle a crashed SQL Server, so you'd probably want to implement some kind of periodic checking of the subscriptions - a scheduled task to kick off periodically to make sure each subscriber has a subscription for each device. Not perfect, but probably as close as you can get without either 2005 or going through the sprocs.

    HTH....

    --
    Joe Webb
    SQL Server MVP


    ~~~
    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)


    Monday, August 22, 2005 1:22 PM
  • Shyam posted a very clever solution for this scenario in the newsgroups yesterday.  Here's a link:

     http://groups-beta.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/ef7e87acdd261802/01d8b36a6e2e85f9#01d8b36a6e2e85f9 

    Just wanted to make sure that people in both communities benefited from his insight. Thanks Shyam!

    --
    Joe Webb
    SQL Server MVP


    ~~~
    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)

    Wednesday, August 24, 2005 1:11 PM
  • I saw that, the orig NG post is mine as well. However, it still doesn't solve the lack of transaction support with in the API, and now I'll be going outside of the API to another table. Trying to keep this all in sync will require use of the stored procedures and a sql connection.

    Any chance in the gold release of 2005 the connection can be exposed from within the API so that it can be used to create transactions?
    Wednesday, August 24, 2005 1:19 PM
  • Agreed, transactional support via the API is something that would be very useful. According to another forum thread ("Multiple Devices for a Subscription"), MS has this on their wishlist for a future release - post 2005.

    In the meantime...If you're willing to wait for 2005 *and* you're talking about a simple event driven subscription, you use the new views to create your subscribers, devices, and subscriptions? Something like:


    INSERT NSInstance.dbo.NSSubscriberView(SubscriberId, Enabled)
    VALUES ('joew@webbtechsolutions.com', 1)

    INSERT NSInstance.dbo.NSSubscriberDeviceView (SubscriberId, Enabled, DeviceName, DeviceTypeName, DeviceAddress, DeliveryChannelName)
    VALUES('joew@webbtechsolutions.com', 1, 'EmailDevice', 'Email', 'joew@webbtechsolutions.com', 'EmailChannel')


    INSERT NSApplication.dbo.NS<SubscriptionClassName>View (SubscriberId, Enabled, SubscriberDeviceName, SubscriberLocale, PrType)
    VALUES ('joew@webbtechsolutions.com', 1, 'EmailDevice', 'en-us', 1)

    You can wrap this up in a transaction or better yet, put them in a stored procedure with transactional support that your Sub Mgt App can call.

    Note however that you cannot insert scheduled subscriptions or subscriptions that use condition actions using the NS<SubscriptionClassName>View view.

    HTH....

    --
    Joe Webb
    SQL Server MVP


    ~~~
    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)

     

    Thursday, August 25, 2005 1:59 PM