Re: min_active_rowversion ( was Re: Triggers and Flag bit)

Erland Sommarskog wrote:
> Sharif Islam (mislam@spam.uiuc.edu) writes:
>> Thanks for the help. Here's how I am using it. I created a column 'Flag'
>> with timestamp datatype.
>>
>> ------
>>
>> declare @before timestamp
>> declare @after timestamp
>> set @before= min_active_rowversion() -1
>> update MyTable set MyCol ='Test' where MyCol like 'Test123%'
>> set @after = min_active_rowversion() -1
>>
>> select ID,MyCol from MyCol where Flag -1 < @after
>> and Flag -1 >= @before
>> -----
>> This gave me the list of record ID that was just changed. Is this the
>> way to use min_active_rowversion()?
>
> Hm, not really.
>
> Your Perl script would run a batch like:
>
> DECLARE @new_highwater_mark rowversion
> SELECT @new_highwater_mark = min_active_rowversion()
>
> SELECT ID, MyCol, @new_highwater_mark
> FROM tbl
> WHERE tstamp >= @last_highwater_mark AND
> tstamp < @new_highwater_mark
>
> That is, the Perl script needs to remember @new_highwater_mark, and pass
> it as @last_highwater_mark next time.
>
> It's important to capture min_active_rowversion() into a variable, because
> it could change while the query is running, which could lead to lost
> updates.
>
>
Got it! Thanks a lot.