ansaurus

Question

SQL Server 2005 replication of DBs cross-server -- need to know when a row was inserted/updated on the target server

Answer 1

+2 A:

The easiest way to fix this is probably to update your table to add a timestamp column, and have it default to the current versioing item. You can look here for more information, but the format is basically the following...

ALTER TABLE <tablename> ADD timestamp

As noted in that link, you do not need to specify a column name, as it will automatically start tracking the timestamp during inserts/updates.

Note that this solution does not give you a human readable time - the item given is unique to the (just something that is easy for versioning). If you want a time, you can make a DATETIME column that defaults to GETDATE() (see here for more info). When you insert/update a row, you would just ignore inserting/updating anything in that particular column, and the current DATETIME would be added for you.

allie 2010-07-14 13:01:46

Okay, I was thinking in those lines, just was'nt sure that it would work on the target server in a replication chain?

David 2010-07-14 13:15:49

I've not done it in a set-up like that, however, if you're adding everything to the target server, the timestamps should all be consistent. Replication should strictly copy the table (see here [http://msdn.microsoft.com/en-us/library/ms151198.aspx]), not reinsert the rows into a new table on the replication chain. Thus, the timestamps shouldn't be changed.

allie 2010-07-14 13:20:08

+1 but it doesn't make sense to say "add a timestamp column, and have it default to the current time." It has no time element to it.

Martin Smith 2010-07-14 13:33:29

Edited - alas, `timestamp` was a poor choice of syntax naming regarding this, as it is misleading. Hopefully my clarification helps, but I suspect the `timestamp` bit will continue to throw people off.

allie 2010-07-14 13:35:44

That's why we're supposed to use the ROWVERSION synonym now.http://msdn.microsoft.com/en-us/library/ms182776.aspx

etliens 2010-07-14 20:34:29

Answer 2

A:

Hello Everybody. Many thanks for the answers. I will have to do my homework and look into the timestamp per row issue.

Just to make sure. Will I be able to query the timestamp in an sql query, so that I can get the rows that has been edited/Inserted within my last update?

Furthermore, I do not have access to the source server due to hosting issues. I have a replication set up, and can only manipulate the target server.

By doing this I will be able to see per row, when it was last updated?

many many thx

David

David 2010-07-15 19:03:17

ansaurus

tags:

views:

answers:

SQL Server 2005 replication of DBs cross-server -- need to know when a row was inserted/updated on the target server

related questions