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 14:02:49 +0000 UTC
Newsgroups: microsoft.public.sqlserver.notificationsvcs
More information:

My trigger looks like this:

CREATE TRIGGER NewTrigger
ON MyRecord
FOR INSERT, UPDATE
AS
DECLARE @eventBatchId BIGINT
DECLARE @providerName NVARCHAR(255)
DECLARE @RecordID INT
DECLARE @NSINstalled char(1)
Select @NSInstalled = dbo.IsSSNSInstalled()
IF (@NSInstalled = 'Y')
BEGIN
        SELECT @providerName = N'NewRecordAddedSPProvider'
        SELECT @RecordID = ID from inserted
EXEC [EntropyAlerts].[NSEventBeginBatchNewRecordAdded] @providerName, @eventBatchId OUTPUT
        EXEC [EntropyAlerts].[NSEventWriteNewRecordAdded] @eventBatchId, 
@RecordID
        EXEC [EntropyAlerts].[NSEventFlushBatchNewRecordAdded] @eventBatchId, 1
END
GO



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>