[Top] [All Lists]

Re: SqlProvider issue

Subject: Re: SqlProvider issue
From: Joe Webb
Date: Tue, 28 Aug 2007 04:56:16 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hi Mauro - 

A couple of thoughts along these lines: 

1) If you control the table that is the source of the events, you can add
a column to it that indicates the last time the row was modified (or
inserted). Then you can create a chronicles table that keeps track of the
last time the generator ran and look for only those rows modified since
the last time the generator ran. This works nicely. 

2) A common way to check for changes to a row is to calculate the
BINARY_CHECKSUM value across all the columns of the row and compare to the
previous calculation (which would be stored in a chronicles table). It's
not 100% because theoretically a row could be changed in such a way that
it's checksum doesn't change, but that's highly unlikely. 

3) You can use the SSNS stored procedures to insert events into the events

4) Or you can do it the way you have suggested. 

The best implementation will likely depend on your unique situation, but I
would lean towards 1 or 2 if possible. 


Joe Webb
SQL Server MVP

On Tue, 28 Aug 2007 03:40:18 -0700, Peruukki wrote:

> Hi all,
> I am wondering what's the best practice for the problem I'm facing.
> I'd like to use a scheduled provider that checks if a field is changed
> in a table, but looking at examples it seems that there's no other way
> but keeping a chronicle table with previous values.
> Due to the large amount of data, it does not seem so performant.
> So I was thinking at the possibility to trigger each change in sql
> server, save data in a custom table and let the provider read this
> user-created chronicle table.
> Does it seem reasonable for you or is there a better way to do it?
> Thanks Mauro

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