We have a need to send events out to multiple devices for a single subscription, the user may be able to decide what devices with what subscriptions. So far we have the following two ideas for accomplishing this:
create a multiple subscriptions, one for each device the user wants the events to go to
create a multiple unique device entries for each subscription
So far as I've been able to tell there is no transaction support when using the notification services API, so having to update multiple records for a single subscription can lead to further maintenance issues if the updating were to fail part way through.
Is there a simpler way to have events for a single subscription to go out to multiple devices?
"When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity." - Albert Einstein
not that we have come across. the one device/one subscription is kind of designed in.... But, you could get around this with a custom delivery protocol which interpreted the special deviice names and did multiple sends. eg devicename SMTPFAX means send and email and a fax.
<MeNot...@community.nospam> wrote: >We have a need to send events out to multiple devices for a single >subscription, the user may be able to decide what devices with what >subscriptions. So far we have the following two ideas for accomplishing >this:
>create a multiple subscriptions, one for each device the user wants the >events to go to
>create a multiple unique device entries for each subscription
>So far as I've been able to tell there is no transaction support when using >the notification services API, so having to update multiple records for a >single subscription can lead to further maintenance issues if the updating >were to fail part way through.
>Is there a simpler way to have events for a single subscription to go out to >multiple devices?
Suggestions on how to role my own with the API? I know I could access the Stored procedures directly, but would like to refrain from that, and currently I'm not clear on how to get at the database connection that the API uses internally.
"Joe Webb" <j...@webbtechsolutions.com> wrote in message
> I support PASS, the Professional Association for SQL Server. > (www.sqlpass.org)
> On Thu, 18 Aug 2005 07:42:24 -0400, "Wayne" > <MeNot...@community.nospam> wrote:
>>We have a need to send events out to multiple devices for a single >>subscription, the user may be able to decide what devices with what >>subscriptions. So far we have the following two ideas for accomplishing >>this:
>>create a multiple subscriptions, one for each device the user wants the >>events to go to
>>create a multiple unique device entries for each subscription
>>So far as I've been able to tell there is no transaction support when >>using >>the notification services API, so having to update multiple records for a >>single subscription can lead to further maintenance issues if the updating >>were to fail part way through.
>>Is there a simpler way to have events for a single subscription to go out >>to >>multiple devices?
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'j...@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.
<MeNot...@community.nospam> wrote: >Suggestions on how to role my own with the API? I know I could access the >Stored procedures directly, but would like to refrain from that, and >currently >I'm not clear on how to get at the database connection that the API uses >internally.
>> I support PASS, the Professional Association for SQL Server. >> (www.sqlpass.org)
>> On Thu, 18 Aug 2005 07:42:24 -0400, "Wayne" >> <MeNot...@community.nospam> wrote:
>>>We have a need to send events out to multiple devices for a single >>>subscription, the user may be able to decide what devices with what >>>subscriptions. So far we have the following two ideas for accomplishing >>>this:
>>>create a multiple subscriptions, one for each device the user wants the >>>events to go to
>>>create a multiple unique device entries for each subscription
>>>So far as I've been able to tell there is no transaction support when >>>using >>>the notification services API, so having to update multiple records for a >>>single subscription can lead to further maintenance issues if the updating >>>were to fail part way through.
>>>Is there a simpler way to have events for a single subscription to go out >>>to >>>multiple devices?
Wayne, This is actually quite simple to do. Remember that the match rule is just a join that produces the notification rows. Each row represents a notification going to one device. So if you want to have the same notification sent to multiple devices, you just need to write your match rule in such a way as to produce multiple rows with the same notification data, but different device names. Here's an example of one way to do it:
Keep a table that stores a mapping of subscriptions to devices. For example:
SELECT .... FROM Events e, Subscriptions s JOIN SubscriptionDeviceMappings m where m.SubscriptionId = s.SubscriptionId WHERE ...
If the SubscriptionDeviceMappings table has multiple rows for each SubscriptionId, you'll end up with multiple notification rows.
You'll have to maintain the SubscriptionDeviceMappings table in your subscription management code, but that's really not different to keeping the device name in the subscription itself, which is a very common practice.
> We have a need to send events out to multiple devices for a single > subscription, the user may be able to decide what devices with what > subscriptions. So far we have the following two ideas for accomplishing > this:
> create a multiple subscriptions, one for each device the user wants the > events to go to
> create a multiple unique device entries for each subscription
> So far as I've been able to tell there is no transaction support when > using the notification services API, so having to update multiple records > for a single subscription can lead to further maintenance issues if the > updating were to fail part way through.
> Is there a simpler way to have events for a single subscription to go out > to multiple devices?
> "When a man sits with a pretty girl for an hour, it seems like a minute. > But > let him sit on a hot stove for a minute and it's longer than any hour. > That's relativity." - Albert Einstein
<spat...@online.microsoft.com> wrote: >Wayne, >This is actually quite simple to do. Remember that the match rule is just a >join that produces the notification rows. Each row represents a notification >going to one device. So if you want to have the same notification sent to >multiple devices, you just need to write your match rule in such a way as to >produce multiple rows with the same notification data, but different device >names. Here's an example of one way to do it:
>Keep a table that stores a mapping of subscriptions to devices. For example:
>SELECT .... >FROM Events e, Subscriptions s > JOIN SubscriptionDeviceMappings m where m.SubscriptionId = >s.SubscriptionId >WHERE ...
>If the SubscriptionDeviceMappings table has multiple rows for each >SubscriptionId, you'll end up with multiple notification rows.
>You'll have to maintain the SubscriptionDeviceMappings table in your >subscription management code, but that's really not different to keeping the >device name in the subscription itself, which is a very common practice.
However, this 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?
"When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity." - Albert Einstein
"Joe Webb" <j...@webbtechsolutions.com> wrote in message
> I support PASS, the Professional Association for SQL Server. > (www.sqlpass.org)
> On Tue, 23 Aug 2005 11:28:22 -0700, "Shyam Pather [MSFT]" > <spat...@online.microsoft.com> wrote:
>>Wayne, >>This is actually quite simple to do. Remember that the match rule is just >>a >>join that produces the notification rows. Each row represents a >>notification >>going to one device. So if you want to have the same notification sent to >>multiple devices, you just need to write your match rule in such a way as >>to >>produce multiple rows with the same notification data, but different >>device >>names. Here's an example of one way to do it:
>>Keep a table that stores a mapping of subscriptions to devices. For >>example:
>>SELECT .... >>FROM Events e, Subscriptions s >> JOIN SubscriptionDeviceMappings m where m.SubscriptionId = >>s.SubscriptionId >>WHERE ...
>>If the SubscriptionDeviceMappings table has multiple rows for each >>SubscriptionId, you'll end up with multiple notification rows.
>>You'll have to maintain the SubscriptionDeviceMappings table in your >>subscription management code, but that's really not different to keeping >>the >>device name in the subscription itself, which is a very common practice.
>However, this still doesn't solve the lack of transaction support with in >the API,
Agreed, transactional support via the API is something that would be very useful and I know that MS has it 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:
<MeNot...@community.nospam> wrote: >However, this 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?
|
