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

Re: Duplicate Notifications

Subject: Re: Duplicate Notifications
From: "David"
Date: Tue, 3 May 2005 22:43:03 -0400
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Joe:

Thanks again for the quick follow up.  Our organization has been at NS for 
quite some time (years) and
have a pretty sophisticated subscription management application wrapped 
around it.  I have, in fact, used the
storedprocs to which you refer to try and figure out this issue.  Since my 
last posting we were also trying to follow it in the profiler to little 
avail.

I think that if duplicate subscriptions were the culprit, then running the 
event rule in query analyzer would produce the same rows as are appearing in 
the notification table. Right ? It doesn't though.

To repeat, the storeprocs to which you refer tell me that there was one rule 
firing. The rule firing had at least one notification batch  and that a 
single batch had twice as many notifications as it should have with the 
duplicates paired up in order by Notification ID.  1, Copy 1, 2, Copy 2,....

I whole heartedly agree that it seems like the join between subscriptions 
and events must be returning multiple rows.
First this is tough to do with a distinct in the select. Second, with or 
without the distinct, the rules does not return duplicates in QA.

As information,  DigestDelivery is being used in this scenario.  There is a 
compute  field, but it is based on
a value in the record that is being duped so it should be the same on both 
records.

My thought is that that the Event View that rule actually runs against is 
returning duplicates for some reason.
That or the NSNotify xsp is doing it.  Is there some reason why the events 
would get copied to the current tables twice.
My understanding is that the Event  View is just a flat view of the 
"current" tables ?

I'll tripple check, but I've had multiple sets of gifted eyes trying to 
figure this out.  Any insights into what's going on in the shadows would be 
helpful.

Thanks,
David

"Joe Webb" <[email protected]> wrote in message 
news:%[email protected]
> Hi David -
>
> The following sproc produces 2 resultsets when run in QA. The first shows 
> information about Notification Batch 999 and the second details the 
> notifications produced during that batch.
>
> EXEC NSNotificationBatchDetails N'PrNotifications', 999
>
> BTW - You can look at the NS tables directly if you prefer 
> (NS<NotificationClass>Notifications and 
> NS<NotificationClass>NotificationBatches1) but the sproc is a bit easier 
> to read.
>
> If you have duplicate subscriptions defined, you'll get multiple identical 
> lines in second resultset. (unless you have DigestDelivery turned on and 
> then duplicate notifications will have a non-NULL value for the 
> LinkParentNotification column.)
>
> So you may want to triple check your subscriptions to make sure you don't 
> have multiple defined. By creating some duplicate subscriptions, I was 
> able to repro your problem.
>
> Also, I don't know that I really quite understand the psuedo-code you sent 
> in your last post. Is "e.SelectRuleID = s.SelectRuleID" part of the 
> changes you made before posting? Or is that actually in the rule?
>
>
> I have something like the following for one of my client's SQLNS apps. In 
> this example, the event provider actually provides the PrTitle and 
> PdfFileName and it gets carried around through the notification generation 
> process. That's not the most efficient way to do it, but for this 
> particular example it was the way it was implemented. I'll spare you the 
> details... :)
>
> SELECT dbo.PrNotificationsNotify(
> s.SubscriberId
> , s.SubscriberDeviceName
> , s.SubscriberLocale
> , e.PrTitle
> , e.PdfFileName) FROM PrSubscription s
> INNER JOIN PressRelease e on e.PrType = s.PrType
>
>
> BTW - this will also work.
>
> SELECT dbo.PrNotificationsNotify(
> s.SubscriberId
> , s.SubscriberDeviceName
> , s.SubscriberLocale
> , e.PrTitle
> , e.PdfFileName)
> FROM PressRelease e, PrSubscription s
> WHERE e.PrType = s.PrType
>
>
>
> HTH...
> Joe Webb
> SQL Server MVP
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
>
>
>
> David Van de Vate wrote:
>> Joe:
>>
>> Thanks in advance for your prompt attention to this question. It truly
>> has me baffled.
>>
>> We are quite certain that we are acurately generating events. We're 
>> reasonably certain that we checked the other usual suspects.
>>
>> However, the diagnostics confirm that we generated twice that many
>> notifications.
>>
>> The event rule in question looks something like the following.  When run
>> against a set of events at any point in time, it returns one correct set
>> of notification data.
>>
>> however,  the output from NSNotificationBatchDetails
>> shows 1 NotificationBatch, 1 RuleFiring with what looks to be duplicate
>> notifications in sequence.  That is 1 & 2 are identical, 3 & 4, and so
>> on. It's almost as if statement below were run in parallel against the 
>> same
>> set of events (ie a mutex didn't work).
>>
>> However, setting the generator thread pool size to 1 did not change the
>> outcome.
>>
>> Let me know what else I can pass along.
>>
>>
>>
>> <EventRule>
>>       <RuleName>MyEventRule</RuleName>
>>                     <Action>
>>                         SELECT DISTINCT
>> dbo.MyEventNotificationNotify(s.SubscriberId, s.DeviceName,
>> s.SubscriberLocale, e.UniqueData
>> )
>>                         FROM  MyEvent  s INNER JOIN
>>                                 MyEventEvent e ON (e.SelectRuleID =
>> s.SelectRuleID)
>>                         where e.Customfield matches something else
>> </Action>
>>                     <EventClassName>MyEventEvent</EventClassName>
>>                 </EventRule>
>>
>>
>>
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com *** 



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