microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

RE: Something I don't understand.

Subject: RE: Something I don't understand.
From: MarkSW
Date: Fri, 28 Apr 2006 09:25:02 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
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









"Bob" wrote:

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

<Prev in Thread] Current Thread [Next in Thread>
  • RE: Something I don't understand., MarkSW <=