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

Re: Duplicate Notifications

Subject: Re: Duplicate Notifications
From: Joe Webb
Date: Tue, 03 May 2005 17:27:52 -0500
Newsgroups: microsoft.public.sqlserver.notificationsvcs
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>