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

Re: Inconsistent Double-Ups out of the Generator

Subject: Re: Inconsistent Double-Ups out of the Generator
From: "Jason Liddiard"<>
Date: Mon, 3 Oct 2005 09:02:43 -0500
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hi Kate, pasted below is the match rule I am using. It seems to work fine if 
I disable the Generator, set the subscription chronicle back a day, set the 
quatum clock to the time when my scheduled rule fires, and run the 
NSPrepareRuleFiring PROC. If I run the query manualy (i know,a big no no) it 
returns the correct amount of rows, if I let it go through 
NSExecuteRuleFiring, it can double up the number of notifications. Strange? 
Please let me know if you need anything else posted. 

Regards
Jase

INSERT  INTO NewSaleByTextSubscriptionProcessingTimes(SubscriptionId, 
LastProcessingTime)
                        SELECT  subscriptions.SubscriptionId, subscriptions.
Created
                        FROM    NewSaleByText subscriptions  
                        WHERE   NOT EXISTS (
                            SELECT  chron.SubscriptionId
                            FROM    NewSaleByTextSubscriptionProcessingTimes 
chron
                            WHERE   chron.SubscriptionId = subscriptions.
SubscriptionId
                        )

                        --Do the match.
                        SELECT DISTINCT                 
subscriptions.SubscriberId, 
                                                                                
                subscriptions.DeviceName, 
                                                                
subscriptions.Locale, 
                                                                
sales.CustomerName,
                                                                                
                sales.PostCode,
                                                                                
                sales.SalesUnits,
                                                                                
                sales.PackageDescription
                        FROM    SaleAddedLog eventlog
                                JOIN JACGBHWSERV01.CSL.dbo.vwNS_DailySales 
sales ON eventlog.WWID = 
sales.WWID COLLATE Latin1_General_CI_AS
                                JOIN NewSaleByText subscriptions ON 
subscriptions.WWID = sales.WWID
                                JOIN 
NewSaleByTextSubscriptionProcessingTimes chron ON subscriptions.
SubscriptionId = chron.SubscriptionId
                        WHERE   eventlog.TimeAdded >= chron.
LastProcessingTime AND DATEPART(dd,eventlog.TimeAdded) = DATEPART(dd,
GETDATE())

                        --Update Processing Time Chronicle for those 
                        --subscriptions just processed.
                        UPDATE  NewSaleByTextSubscriptionProcessingTimes
                        SET     LastProcessingTime = GETUTCDATE()
                        FROM    NewSaleByTextSubscriptionProcessingTimes 
chron
                                JOIN NewSaleByText subscriptions ON chron.
SubscriptionId = subscriptions.SubscriptionId

                        --Clear down the SaleAddedLog (the Event Chronicle 
table)
                        DELETE FROM SaleAddedLog
                        WHERE DATEPART(dd,TimeAdded) < DATEPART(dd,GETDATE())

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