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

Re: Using SPs to fire events - but the notifications dont get sent!

Subject: Re: Using SPs to fire events - but the notifications dont get sent!
From: "Mwob"
Date: 18 May 2006 13:32:33 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hi

It creates 2 event batches, and 2 events (one for each batch). Thats
because each update/insert trigger is set to start a new batch, add the
event, and then complete the event batch.

Does that make a difference?


ramadu wrote:
> In the scenario where it does the insert & update in succession, does it
> create two Eventbatchids or just one?
>
> - ramadu
>
> :
> > Hello ramadu,
> >
> > Thanks very much for the assistance - I'll go though each of your
> > checkpoints:
> >
> >> 1. You are calling the NSEventBeginBatchTaskEvents SP before writing
> >> events to the SSNS DB.
> > Yep, thats correct
> >> 2. The provider name for the NSEventBeginBatchTaskEvents is correct
> >> and set to 'SQLTriggerEventProvider'.
> > Well the provider anme is correct, in my instance its called
> > "NewRecordAddedSPProvider", but thats just a name, so thats OK
> >> 3. You are using the correct EventBatchId (which is returned as output
> >> of NSEventBeginBatchTaskEvents) while writing the events to the SSNS.
> > Yep
> >> 4. You are calling the NSEventFlushBatchTaskEvents with the correct
> >> EventBatchId before the trigger exits.
> > Yep
> >
> > Still no joy - but it gets weirder. If I have the trigger so that it
> > fires for both update and insert, and then test by adding a row manually
> > in SQL Server management studio, the event adds OK, and I get the
> > notification OK. If I then change this SQL so that immediatly after
> > doing the insert, it performs an update on the new record, then again I
> > get 2 sets of event batches being added (as expected), but no
> > notifications are fired :(
> >
> > If I then tty to change thios code so that instead of adding and the
> > updating a record, it just adds 2 records quickly in succession, then
> > this also works - I get 2 notifications!
> >
> > Any ideas?
> >
> > Thanks
> >
> > Matt
> >
> >> Hi Matt,
> >>
> >> It should not matter how many notifications are sent at any given
> >> moment to the SSNS database. SSNS should be able to process all the
> >> events and send out notifications. However, there are some basic
> >> things that you can check while writing events to the SSNS database
> >> from your trigger:
> >>
> >> 1. You are calling the NSEventBeginBatchTaskEvents SP before writing
> >> events to the SSNS DB.
> >> 2. The provider name for the NSEventBeginBatchTaskEvents is correct
> >> and
> >> set to 'SQLTriggerEventProvider'.
> >> 3. You are using the correct EventBatchId (which is returned as output
> >> of NSEventBeginBatchTaskEvents) while writing the events to the SSNS.
> >> 4. You are calling the NSEventFlushBatchTaskEvents with the correct
> >> EventBatchId before the trigger exits.
> >> If this doesn't work, maybe you can try splitting the triggers into
> >> two (one for INSERT and the other for UPDATE).
> >>
> >> Hope that helps!
> >>
> >> - ramadu
> >>
> >> :
> >>
> >>> Hi,
> >>>
> >>> I hope someone can help me! I have setup triggers on a table, so that
> >>> when the table is inserted to or updated, it fires an event to my
> >>> SSNS application, which should then send a notification email out.
> >>>
> >>> For some reason, the notifications are not being sent out. I cannot
> >>> find any evidence of them being processed at all. When I look in
> >>> NSEventBatchView, I can see that actually 2 event batches were passed
> >>> through for the same record being added to my application. This is
> >>> because my application adds the new record and then immediatly
> >>> updates something in it - so the trigger fires for an insert and then
> >>> an update straight after. No notifications are sent at all for these
> >>> 2 event batches.
> >>>
> >>> However, if I change the trigger so that it only fires on insert, the
> >>> notifications are sent and everything is fine! Just one event batch
> >>> is processed (as expected) and then then notification is sent out.
> >>>
> >>> Does anyone why having the trigger fire on insert and update would
> >>> cause
> >>> no notifications? Could it be something to do with them firing so
> >>> soon
> >>> after each other (within 3 hundreths of a second)?
> >>> I'm hoping that the asnwer is obvious, and some of you helpful SSNS
> >>> experts can help me out here :)
> >>> Thanks in advance,
> >>>
> >>> Matt.
> >>>
> > 
> >


<Prev in Thread] Current Thread [Next in Thread>