SQL Merge Replication: How to tell if a record has been replicated

Question

I have a merge replication scenario, with SQL2005 SP3 as the publisher and distributor and windows mobile SQLCE 3.5 SP1 as the subscribers.

The replication filter is such that rows from one of the tables (tblJobs) only go to exactly one device. After the device has replicated it executes sql to set the IsSynced boolean column on all the records it has downloaded, then it replicates again.

Is there any way I can avoid the second replication and have the IsSynced boolean column set during the replication?? (Maybe download first, set the flag, then upload? Or maybe have the replication process on the publisher/distributor set the flag?). I don't know where the hooks are for this sort of thing.

It might be worth mentioning the table has a rowversion field.

Answer 1

The easiest way to tell if a record has been replicated is to check the MSmerge_contents table. If the row exists in that table then it has been previously replicated.

Of course, if you want to ensure that the client has the latest version of the row then you'll need to do some digging in the following tables for generation numbers:

MSmerge_replinfo
MSmerge_genhistory
MSmerge_contents
MSmerge_tombstone

Every time data is changed, Merge replication keeps track of this change by assigning a new generation number to the change. The publisher then keeps a list of generation numbers and the generation that each subscriber has received.

Simply doing a batch update of row data on the subscriber will force that change to be replicated to the publisher.

Unfortunately, I don't know of an easy way to update that column unless you don't replicate that column in the first place - that is, exclude it using vertical (column) partitioning.

Answer 2

Not sure what you really want, but in case you want to tell apart those records that originated on this instance from the ones that arrived here from other instances via replication, one way to do this is to have an Origin column in your table and have it defaulted to a unique value on each of your servers.