Multiple EMails per Subscriber

Is it possible to have a subscriber specify more than one e-mail address for a subscription? ie, they want a notification sent to two different email accounts.

Thanks,

Dan

Yes, it is possible, but a much easier solution to this problem would be to treat these 2 email addresses as if they belonged to 2 different subscribers.

In other words, it's much easier: 1 email address == 1 subscriber. Although those 2 distinct subscribers can be 1 and the same "physical" person.

It's all managed by SMI classes.

|||Sure. Each subscriber can have multiple subscriberdevices associated with them. Just mind the match rule.

Joesql

Multiple Devices per subscription and SQL-NS API communication

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

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

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)

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

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)

|||

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)

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

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)

Multiple devices for a subscription

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:

1) create a multiple subscriptions, one for each device the user wants the events to go to
2) 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?

Typically you want to separate out subscriber management from subscription management in your application.

In subscriber management you would want the subscribers to configure which devices they have. This typically would happen only once and this list of devices is maintained only once in the instance database/schema. This list of subscribers and their respective devices is common across all applications and maintained only once per instance.

Yes, during subscription management the subscriber would need to create a subscription for each of their devices (your option 1), to ensure the notification is sent to each device.

You correctly cite, there is no transaction support when adding devices or subscriptions. We will evaluate support for for this in next release as we have received this request before in different forms.

Thanks,
Anand

|||

Next release? you mean the 2005 gold release? or next version of SQL server and notification services?

We need the ability to show the user one subscription with multiple devices, so we will handle creating the devices and multiple subscriptions on the back end, and to abtain transaction support we may have to go directly to the stored procedures for the time being.

|||Sorry, I should have been more clear. By next realease I meant the release AFTER SQL Server 2005.|||

Check out this related thread. It links to a posting from Shyam that offers a clever alternative for the current version.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=72056
--
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)