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: matt roberts
Date: Thu, 18 May 2006 15:50:11 +0000 UTC
Newsgroups: microsoft.public.sqlserver.notificationsvcs
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>