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: "Colin Meek"
Date: Tue, 4 Oct 2005 08:43:16 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
There is also a possibility you will miss some notifications. Events 
arriving within a particular quantum are not processed immediately, so 
setting the "last processed time" to the current time in your 
NewSaleByTextSubscriptionProcessingTimes chronicle is not safe. For 
instance, the quantum 12:00-12:05 quantum might be processed at 12:10, which 
in your implementation would mean no events arriving between 12:05-12:10 are 
ever matched.

-- 
Colin Meek
SQL Server Notification Services

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Joe Webb" <[email protected]> wrote in message 
news:[email protected]
> Hi Jason -
>
> Thanks for posting the additional information and description of what
> you're doing. That helps a lot.
>
> Is there a reason you're using GETDATE() in the WHERE clause rather
> than GETUTCDATE()?
>
> Are you Digest Delivery?
>
> -- 
> Joe Webb
> SQL Server MVP
> http://www.sqlns.com
>
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
>
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
>
>
> On Tue, 4 Oct 2005 05:00:47 -0500, "Jason Liddiard"<[email protected]>
> wrote:
>
>>Apologies for my last post, I had copied the query from QA and while 
>>testing
>>had removed the call to the Notify function.
>>
>>What I have is sales data in a table on a remote server. I have a 
>>scheduled
>>SQL event provider that run everyday at 6am to collect all the WWID 
>>numbers
>>( Employee ID's) for the days sales. An employee may have several rows of
>>data with no unique ID for each row, so I collect only the Distinct WWID
>>numbers. I add this to the events chronicle table, SaleAddedLog along with
>>the time it was added.
>>
>>The scheduled rule is set to run at 10am. This should look at all the WWID
>>numbers collected, JOIN with the Sales data, Subscriptions and 
>>Subscriptions
>>Chron table to produce only notifications for sales for today's date hence
>>the where clause testing for events greater than the last time subs were
>>processed and events must be todays date. The where clause is meant to
>>prevent doubleups should notifications not be processed on any day, as the
>>next day, two wwid numbers would cause multiple duplications on the join.
>>
>>Not the most graceful solution I am sure, but with Unique ID's for each 
>>row
>>of sales data, I could not think of an alternative solution. Any 
>>suggestions
>>would be gratefully appreciated.
>>
>>
>><SubscriptionClasses>
>>  <SubscriptionClass>
>>  <SubscriptionClassName>NewSaleByText</SubscriptionClassName>
>> <Schema>
>>  <Field>
>>   <FieldName>DeviceName</FieldName>
>>   <FieldType>NVARCHAR(255)</FieldType>
>>   <FieldTypeMods>not null</FieldTypeMods>
>>  </Field>
>>  <Field>
>>   <FieldName>Locale</FieldName>
>>   <FieldType>NVARCHAR(10)</FieldType>
>>   <FieldTypeMods>NOT NULL</FieldTypeMods>
>>  </Field>
>>  <Field>
>>   <FieldName>WWID</FieldName>
>>   <FieldType>INT</FieldType>
>>   <FieldTypeMods>NOT NULL</FieldTypeMods>
>>  </Field>
>> </Schema>
>>
>><ScheduledRules>
>> <ScheduledRule>
>>  <RuleName>MatchSalesForText</RuleName>
>>  <Action>
>> --Insert rows into Processing Times chronicle
>> --for new subscriptions.
>> 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 dbo.NewSaleSMSDeliveryNotify(
>>                subscriptions.SubscriberId,
>> subscriptions.DeviceName,
>>                subscriptions.Locale,
>>                sales.CustomerName,
>> sales.PostCode,
>> sales.SalesUnits,
>> sales.PackageDescription)
>>FROM SaleAddedLog eventlog
>>JOIN JACGBHSERV01.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 &gt;= 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) &lt; DATEPART(dd,GETDATE())
>>  </Action>
>> </ScheduledRule>
>></ScheduledRules> 



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