Glad you found it useful. I look forward to seeing your results.
SQL Server MVP
On Tue, 28 Aug 2007 19:47:09 +0000, Peruukki wrote:
> Thanks Joe,
> I think I will investigate the first and the third solution you gave
> and I will try to use the fourth too.
> I don't know if it can be useful to post code for each solution, but
> if someone needs help on the subject I can post the skeleton of a
> possible implementation.
> Thanks a lot
> On 28 Ago, 13:56, Joe Webb <[email protected]> wrote:
>> 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 MVPhttp://weblogs.sqlteam.com/joew/
>> 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