| Groups | Blog | Home |
sql server notification services : Multiple devices for a subscription
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? -- Thanks Wayne Sepega Jacksonville, Fl Enterprise Library Configuration Console Module Generator http://workspaces.gotdotnet.com/elccmg "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. hope that helps Kate MBCS Alert Technologies http://www.alert-technologies.co.uk Get your notification services implementation going in minutes not weeks ......
Wayne -
As Kate mentioned in another post, a custom delivery protocol may address your delivery issues. As for transactional support for the NS API, you're right it's not built in. But you can always roll your own if need be. 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) On Thu, 18 Aug 2005 07:42:24 -0400, "Wayne" [quoted text, click to view] <MeNotYou@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
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. [quoted text, click to view] "Joe Webb" <joew@webbtechsolutions.com> wrote in message news:758cg19c8sg5k44k7gnm245une5euhe2ri@4ax.com... > Wayne - > > As Kate mentioned in another post, a custom delivery protocol may > address your delivery issues. > > As for transactional support for the NS API, you're right it's not > built in. But you can always roll your own if need be. > > 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) > > > On Thu, 18 Aug 2005 07:42:24 -0400, "Wayne" > <MeNotYou@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 -
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) On Fri, 19 Aug 2005 16:01:32 -0400, "Wayne" [quoted text, click to view] <MeNotYou@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. > > >"Joe Webb" <joew@webbtechsolutions.com> wrote in message >news:758cg19c8sg5k44k7gnm245une5euhe2ri@4ax.com... >> Wayne - >> >> As Kate mentioned in another post, a custom delivery protocol may >> address your delivery issues. >> >> As for transactional support for the NS API, you're right it's not >> built in. But you can always roll your own if need be. >> >> 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) >> >> >> On Thu, 18 Aug 2005 07:42:24 -0400, "Wayne" >> <MeNotYou@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: CREATE TABLE SubscriptionDeviceMappings ( SubscriptionId bigint, SubscriberId nvarchar(255), DeviceName nvarchar(255) ) In your match rule, you simply join like this: 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. 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] "Wayne" <MeNotYou@community.nospam> wrote in message news:eEkTon%23oFHA.3760@TK2MSFTNGP15.phx.gbl... > 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? > > > -- > Thanks > Wayne Sepega > Jacksonville, Fl > > Enterprise Library Configuration Console Module Generator > http://workspaces.gotdotnet.com/elccmg > > "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 > >
Ah....Very nice solution! 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) On Tue, 23 Aug 2005 11:28:22 -0700, "Shyam Pather [MSFT]" [quoted text, click to view] <spather@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: > >CREATE TABLE SubscriptionDeviceMappings >( > SubscriptionId bigint, > SubscriberId nvarchar(255), > DeviceName nvarchar(255) >) > >In your match rule, you simply join like this: > >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. > >Hope this helps.
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? -- Thanks Wayne Sepega Jacksonville, Fl Enterprise Library Configuration Console Module Generator http://workspaces.gotdotnet.com/elccmg "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 [quoted text, click to view] "Joe Webb" <joew@webbtechsolutions.com> wrote in message news:j2rmg1lanprspdlljl3bgp181tme62c2qm@4ax.com... > Ah....Very nice solution! 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) > > > On Tue, 23 Aug 2005 11:28:22 -0700, "Shyam Pather [MSFT]" > <spather@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: >> >>CREATE TABLE SubscriptionDeviceMappings >>( >> SubscriptionId bigint, >> SubscriberId nvarchar(255), >> DeviceName nvarchar(255) >>) >> >>In your match rule, you simply join like this: >> >>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. >> >>Hope this helps. >>-shyam
found shyams comments on creating multiple notification rows very
interesting. i think we can find a use for this. Kate MBCS Alert Technologies http://www.alert-technologies.co.uk Get your notification services implementation going in minutes not weeks ......
[quoted text, click to view]
>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: 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) On Wed, 24 Aug 2005 09:20:49 -0400, "Wayne" [quoted text, click to view] <MeNotYou@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
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |