|
|
You're in the
sql server notification services
group:Using SPs to fire events - but the notifications dont get sent!
sql server notification services:
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.
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 : [quoted text, click to view] > 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. >
In the scenario where it does the insert & update in succession, does it
create two Eventbatchids or just one? - ramadu : [quoted text, click to view] > 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. >>> >
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? [quoted text, click to view] 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. > >>> > > > >
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 [quoted text, click to view] > 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. >
Hello ramadu,
Thanks very much for the assistance - I'll go though each of your checkpoints: [quoted text, click to view] > 1. You are calling the NSEventBeginBatchTaskEvents SP before writing Well the provider anme is correct, in my instance its called "NewRecordAddedSPProvider", > events to the SSNS DB. Yep, thats correct > 2. The provider name for the NSEventBeginBatchTaskEvents is correct > and set to 'SQLTriggerEventProvider'. but thats just a name, so thats OK [quoted text, click to view] > 3. You are using the correct EventBatchId (which is returned as output Yep> of NSEventBeginBatchTaskEvents) while writing the events to the SSNS. Yep > 4. You are calling the NSEventFlushBatchTaskEvents with the correct > EventBatchId before the trigger exits. 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 [quoted text, click to view] > 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. >>
just wondering whether opening a new event batch while events are being
written through another can cause a problem. maybe you can manually try to simulate that and see whether events are being written. - ramadu : [quoted text, click to view] > 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. >>>>> >>>
one more thing ... i already asked you to check whether you are calling
the NSEventFlushBatchTaskEvents sp, but can you check in the event batch table that the EndCollectionTime is set for all the event batches? btw, i've reached the limits of my debugging skills and knowledge of SSNS. so, that's it from me. :( - ramadu : [quoted text, click to view] > just wondering whether opening a new event batch while events are being
> written through another can cause a problem. maybe you can manually try > to simulate that and see whether events are being written. > > - ramadu > > : >> 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. >>>>>> >>>>
Hello ramadu,
Thanks for all the help you offerered! I devled more into the EventBatch view as you suggested. The endCollectionTime was set OK, but I realised that the rule firings were failing. When I examined the rules in more detail I discovered that it was trying to log the processing of the record, but there is a primary key set on my logging table, and since it was trying to add the same record twice it was falling over, hence no notifications! Thanks for nudging me in that direction :) [quoted text, click to view] > one more thing ... i already asked you to check whether you are > calling the NSEventFlushBatchTaskEvents sp, but can you check in the > event batch table that the EndCollectionTime is set for all the event > batches? > > btw, i've reached the limits of my debugging skills and knowledge of > SSNS. so, that's it from me. :( > > - ramadu > > : > >> just wondering whether opening a new event batch while events are >> being written through another can cause a problem. maybe you can >> manually try to simulate that and see whether events are being >> written. >> >> - ramadu >> >> : >> >>> 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. >>>>>>>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |