How to track replicated row time for subscribers in SQL Server 2005?

Question

The main problem is this:
The caller successfully replicated the row from the publisher using transactional replication. Now, how do we track the time of the last successful replication of this string?

A friend suggested the following solution, which he used for his SQL Server 2000:
1) Add a datetime column.
2) Modify the replication stored procedure to update the datetime (!) Column.

In step # 2, all sorts of warning signals warn me, so I ask if there are any better solutions for SQL Server 2005 in this situation before I even look at its solution in detail.

Answer 1

I would do exactly what your friend suggested. Thus, only calls to the replication procedure will update the timestamp.

The problem with this approach is that you need write lock, but I don't see any other practical way.

Otherwise, you could use a trigger that fires when a row is retrieved (don't quote me on this, I rarely use triggers), but that doesn't seem right (you can put an end to false positives)

Answer 2

I had this exact problem a few weeks ago while trying to find recently modified records.

TIMESTAMP. SS2005 . , , , ( , ). , , .

, , .

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

, ~

Answer 3

, , ?

Answer 4

@Philippe: , , - - . , , .

, , .

- , : .

In my case, we decided to initialize the snapshot manually to save the added datetime column in the subscriber database. Another possible approach might be to allow initialization, but modify existing stored procedures to ignore replication of the added datetime column.