Multiple devices for a subscription

[Wayne]

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

[kate]

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 ......

[Joe Webb]

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)

[Wayne]

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" <jo...@webbtechsolutions.com> wrote in message
news:758cg19c8sg5k44k7...@4ax.com...

[Joe Webb]

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'jo...@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)

[Shyam Pather [MSFT]]

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.
---------------------------------------------
"Wayne" <MeNo...@community.nospam> wrote in message
news:eEkTon%23oFH...@TK2MSFTNGP15.phx.gbl...

[Joe Webb]

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)

[Wayne]

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

"Joe Webb" <jo...@webbtechsolutions.com> wrote in message
news:j2rmg1lanprspdllj...@4ax.com...

[kate]

found shyams comments on creating multiple notification rows very
interesting. i think we can find a use for this.

[Joe Webb]

>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 ('jo...@webbtechsolutions.com', 1)

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

INSERT NSApplication.dbo.NS<SubscriptionClassName>View (SubscriberId,
Enabled, SubscriberDeviceName, SubscriberLocale, PrType)
VALUES ('jo...@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)