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

Re: Inconsistent Double Notifications

Subject: Re: Inconsistent Double Notifications
From: Joe Webb
Date: Thu, 20 Oct 2005 15:08:20 -0500
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Simon - 

Is your dup issue resolved now? 

-- 
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 Fri, 14 Oct 2005 07:54:01 -0700, "[WRX]Raven"
<[email protected]> wrote:

>Maybe to clarify:
>
>Case has one record with CaseId
>CaseTransactionInfo has potentially multiple entries for a CaseId BUT
>TransactionInfo with it's restrictions will limit the overall result to one 
>record.  Kind of hard to explain, but if you pull out the duplicate 
>notification information and run it through the three table join you get the 
>one expected record returned.
>
>The event table has one entry, the notification table shows two identicical 
>records put in almost simultaneously.  I don't think it is a coding or data 
>issue on my side of the fence.  Possibly something within the NS system 
>tables?  I do have a vaccum scheduled, but cannot think of anything that is 
>giving such inconsistent results.  We have had duplicates now for 4 days 
>running...Monday was fine!  No code changes have been made.
>
>"[WRX]Raven" wrote:
>
>> I even added a DISTINCT on the call just in the off chance it might help a 
>> while back!
>> 
>> There is a single event...and the rule only matches to one record.  I am not 
>> using a chronicle table.
>> 
>> This works, had worked without issue for a couple of months...and then poof 
>> just went flakey.  I am starting today to architect our own version of 
>> this...so if you can help any it would head of some development work there!  
>> My thinking is that the DLL used by SQL Server is causing issues and the 
>> server needs a reboot...but I am not clear what it does, or why it is there.
>> 
>> Thanks! 
>> 
>>                      <EventRules>
>>                              <EventRule>
>>                                      
>> <RuleName>CaseSubscriptionEventRule</RuleName>
>>                                      <Action>
>>                                              SELECT  DISTINCT 
>> dbo.CaseNotificationNotify(cs.SubscriberId, 
>> cs.DeviceName, cs.SubscriberLocale, ce.CaseId, ce.ExternalCaseNumber, 
>> ce.BIN, 
>> ti.TransactionAmount, ce.CaseState)
>>                                              FROM    dbo.CaseEvent ce
>>                                                              INNER JOIN 
>> dbo.CaseSubscription cs
>>                                                              ON ce.CaseId = 
>> cs.CaseId
>>                                                              INNER JOIN 
>> CaseManagement.dbo.[Case] c
>>                                                              ON c.CaseId = 
>> ce.CaseId
>>                                                              INNER JOIN 
>> CaseManagement.dbo.CaseTransactionInfo cti
>>                                                              ON c.CaseId = 
>> cti.CaseId
>>                                                              INNER JOIN 
>> CaseManagement.dbo.TransactionInfo ti
>>                                                              ON 
>> ti.TransactionId = cti.TransactionId AND
>>                                                                 
>> ti.TransactionCode IN ('05', '06', '07') AND
>>                                                                 ti.UsageCode 
>> = 1
>>                                      </Action>
>>                                      
>> <EventClassName>CaseEvent</EventClassName>
>>                              </EventRule>
>>                      </EventRules>
>> 
>> "Joe Webb" wrote:
>> 
>> > Hi Simon - 
>> > 
>> > Are there duplicate rows in the notification table? If so there's
>> > probably some issue with the matching rule and/or the data it's using
>> > to match on.  Can you post your matching rule? 
>> > 
>> > Your post seems to indicate that you've eliminated the possibility of
>> > duplicate rows in the event table. Are you using a chron table? 
>> > 
>> > 
>> > HTH...
>> > 
>> > -- 
>> > 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 Thu, 13 Oct 2005 21:11:01 -0700, "[WRX]Raven"
>> > <[email protected]> wrote:
>> > 
>> > >I have had an NS application running for a while now in a beta mode.  In 
>> > >the 
>> > >past two weeks the end users are getting events twice in their 
>> > >notification 
>> > >emails.  The event table has one entry, the rule will generate just one 
>> > >notification.
>> > >
>> > >I have been through the debug steps in Shyam's book from SAMS and it 
>> > >shows 
>> > >everything working as expected.  One day I bounced the NS service and the 
>> > >next day the notifications were back to normal.  I setup a job to bounce 
>> > >the 
>> > >service each night and that eventually didn't fix the problem.  I did a 
>> > >similar thing with DBCC FREE on the NS dll installed on the SQL Server, 
>> > >again 
>> > >I got a good set of notifications before it failed to fix the issue.  
>> > >This is 
>> > >a production SQL Server that simply cannot be rebooted without extensive 
>> > >client notifications and consequences so if it is a memory/system issue I 
>> > >might be stuck.
>> > >
>> > >Has anyone run into anything similiar or have any ideas where to look or 
>> > >poke around?  We are pretty close to scrapping the whole NS application 
>> > >and 
>> > >writing our own that would be easier to maintain and troubleshoot.
>> > >
>> > >TIA
>> > >Simon
>> > 

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