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 criteria in WHERE clause (was "T-SQL")

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?Uhmm...how do you ever expect Field1 to equal 1 AND equal 2 at the same time? Curious mathematical alternate universe you live in, but since your server seems to be bound to this particular reality, I suggest you look up the "OR" clause in Books Online.|||Maybe you can put this statement inside of a loop and run it until @.@.rowcount > 0. If it runs long enough, math rules might change to include an uncertaintly principal where a finite object can have more than one value at one time. Just an idea.|||blindman,

Actual Field1 is the QuestionID and Field2 is the Answer

Basically one question would have 5 different choice of ans

Ex: Question1 Ans: Excellent, Good....etc

So i need to say in my query that return all rows where QuestionID 1 has Excellent QuestionID2 has Excellent|||Breaking out the crayons today...
WHERE (dbo.Field1= 1 AND Field2= 'Test')
OR (dbo.Field1= 2 AND Field2= 'Test')
OR (dbo.Field1= 3 AND Field2= 'Test')
OR (dbo.Field1= 4 AND Field2= 'Test')
OR (dbo.Field1= 5 AND Field2= 'Test')
...which is logically equivalent to: WHERE dbo.Field1 = IN (1, 2, 3, 4, 5) AND Field2= 'Test'

...I smell an MS Access GUID addict who thinks every new line is a separate predicate. You need to read up on SELECT statements in Books Online before you go any further with SQL programming.|||blindman,

No, i'm not breaking the Crayons today :-)

I understand what you are saying in your query but in my case it won't work since i'm trying to find all questions which has answer = Excellent.

This is how the questions are ask
Question1 :...........?
Ans: you are given five diff choice and ask to choose one|||Dude, I see references to "Field1", "Field2", 1, 2, 3, 4, 5, and 'test' in your query. Nowhere do I see any reference to 'Excellent'. It is also becoming more and more obvious that you have not even tried to figure this out yourself using the resources available in Books Online. So either do a little more research, or state your question more clearly.|||This query is obviously the output of a tool like Access. The solution in SQL would be simple. It's time to take the leap from GUI created logic to writing your own SQL. You will be glad after the initial learning curve.

OR

You could jump over to the Access forums and get better help.|||No, I don't think this is direct Access output, because it doesn't make syntactical sense. I think it is the output of someone who thinks that if they format their SQL statement to LOOK like the Access Query GUI grid it will behave like an Access Query.
Regardless, it's time to either take off the training wheels or hire a chauffeur to drive you around. Books Online.|||blindman,

Thanks for your help. I figure it out.

And for your quote " it's time to either take off the training wheels or hire a chauffeur to drive you around."

I think u need to take your quote for ur self since i figure out the query without online book reference.|||Well, gosh, how great for you. Maybe next time you can do that BEFORE posting such a simplistic question.|||Do I have to send you to your rooms?

Good Lord.

Dude, did you read the sticky at the top of the forum? It would really help us if you follow that model...

Blind dude...at least buy them dinner first....|||Heck no, it's dutch or nothin'.

multiple connects to database

How does reporting services handle connection strings. There is a single report using a shared datasource. Testing this report seems to generate multiple connections to the database for a single user. The report being tested has 4 datasets (3 to obtain parameter value lists) using the same shared datasource but the number of connections for the single user were 20 in one instance. When does reporting services make the connection and when does it release it? It didnt seem to let these go when the report was done executing. Using RS2005 sp1 with DB2The Report Server processes the dataset queries in parallel whenever possible. This is why you see three connections open when you run a report with multiple datasets. Not sure why the number of connections jump to 20. Does the DB2 provider support connection pooling?