sql server notification services: Something I don't understand. -- sql server notification services

Re: Something I don't understand.

Joe Webb
1/24/2006 5:01:13 PM

sql server notification services: Hi Bob -

[quoted text, click to view]

That's true. SQLNS is a develoment framework so you cannot really pick
and choose what portions you'd like to implement. However, in many (or
most) cases you can pretty easily integrate SQLNS into your existing
process with little duplication. For instance, since you already have
your data in a specific table, you can use that information as the
basis for your events. You don't have to submit all the data to the
SQLNS tables, only a primary key for your table and let the SQLNS join
to it later on in the notification process.

Additionally with SQLNS 2005, we can opt to have all the SQLNS-related
database objects to be created in our existing databases. I discuss
this option here
(http://sqlns.blogspot.com/2006/01/using-existing-database-for-our-sqlns.html).

HTH....

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

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

[quoted text, click to view]

Something I don't understand.

Bob
1/24/2006 5:41:52 PM

Well lots of things really but anyways ;-)
I have an existing app using sql server 200 tables. One of the tables is
DeliveriesToBeMade, in it I have all the information about a scheduled
delivery (whoto, when, what, HowToNotify - phone or e-mail, phone number
e-mail address etc.) I'm looking at the notification services docs and I
see that the subscribers database needs to be created and the subscription
tables get created by executing command and and or XML files and all this
underlying databse and underlying tables get created like in the samples.
Fine, but I already got the subscribers. Its my customer list and we don't
want to create an parrallel data entry system for stuff we already have.
There's already an app doing that.
I already have the DeliveriesToBeMade, every time a new delivery is
scheduled in the existing app a new record is created here and it contains
all the info I need to pass to the subscriber. I want the notifications to
go out twice a day, once at 1630hrs and once at 1830 hours.

So question

It looks like I need to use the automatically generated infrastructure for
notifications services and pass it information I already have in effcet
making duplicates but in another form. Am I right? If yes, how can I do
that. If No, same question :-).

Thanks for any insights,

Bob

Re: Something I don't understand.

Bob
1/25/2006 11:47:20 AM

Thanks Joe,
I'm stuck for the time being with utilising sql server 2000, because that's
what the customer's 'new' application uses (developped by another company).
In any case, thanks for your insights.
In the docs and samples they show you existing complete app but nowhere did
I find instructions on how to proceed step by step from scratch, except
possibly in a fairly high level lingo that's not at all clear to a newbie
like me.
Do you know of any place where I can find such information?

Bob

[quoted text, click to view]

Re: Something I don't understand.

Joe Webb
1/30/2006 11:44:29 AM

Hi Bob -

To create a new SQLNS instance you've got a couple of options.

1) you can take one of the existing sample apps, remove all their
logic, and replace it with your own logic. This is typically what I
do.

2) you can essentually automate option #1 by using the CopySample
command to copy an existing sample instance.

3) you can develop from scratch. Both Shyam and I describe how to do
this in our books.

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

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

[quoted text, click to view]

RE: Something I don't understand.

MarkSW
4/28/2006 9:25:02 AM

Another approach I've used with success in production applications in the
same situation is a sort of "minimum Subscriber/Subscription integration"
with the NS Application. You can have an EventProvider do the work of
matching whatever Event data you have to your external Subscriber (or "user"
etc.) data, wherever you currently have it housed. For each match, you also
have the EP call a stored proc which executes this algorithm:
- If there is a record in the NSSubscriber, NSSubscriberDevices, and the
necessary NSSubscriptions table (for the matched SubscriptionClass), then do
nothing
- Else, insert rows into these three tables for this user

Thus, for the cost of one additional SP call, which is almost a no-op after
the first time a user matches, you have eliminated the dependency on needing
to maintain/backfill/insert Subscribe and Subscription data in NS. You can
even rebuild the NS appplication, start it up and it will just work.

NOTE: This isn't recommended or supported by NS because they discourage
direct manipulation and inserts into the NS tables. But I've had great
success with it and no problems for the last couple of years.

For NS 2000 the sample code looks like this:

...
...

EXEC MyInstanceDBMain.dbo.NSGetSubscribers @SubscriberId
IF @@ROWCOUNT = 0
BEGIN
EXEC MyInstanceDBMain.dbo.NSInsertSubscriber @SubscriberId, @Enabled

IF(@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
...
END
END

-- Test for SubscriberDevices record, This always updates email address to
make sure it is in synch if user changes
-- If SubscriberDevices vs. Subscriber data is out of synch, does an
insert, otherwise does an update
EXEC MyInstanceDBMain.dbo.NSGetSubscriberDevices @SubscriberId
IF @@ROWCOUNT = 0
BEGIN
EXEC MyInstanceDBMain.dbo.NSInsertSubscriberDevice @SubscriberId, 'MDC
EmailAddress', 'SMTP', @DeviceAddress, 'EmailChannel'

IF(@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
...
END
END
ELSE
BEGIN
EXEC MyInstanceDBMain.dbo.NSUpdateSubscriberDevice @SubscriberId, 'MDC
EmailAddress', 'SMTP', @DeviceAddress, 'EmailChannel'

IF(@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
...
END
END

-- Always delete and reinsert Subscription rows for this Subscriber,
because this also contains DeviceAddress, email address,
-- which we want to keep in synch
DECLARE @SubscriptionId bigint
EXEC
MyInstanceMyApplicationDB.dbo.NSDeleteMyApplicationDBSubscriptionsSubscriptionsbySubscriberId @SubscriberId
IF(@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
...
END

EXEC
MyInstanceMyApplicationDB.dbo.NSInsertMyApplicationDBSubscriptionsSubscription
@SubscriptionId, @Enabled, 'MDC EmailAddress', @DeviceAddress, 'en-us',
@SubscriberId
IF(@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
...
END

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server notification services group.