I have 2 SQL 2000 servers (both have SP4) and are running on Win2003 SP1. We will call them SQL1 and SQL2.
SQL1 is the publisher and distributor for trans replication, SQL2 is the subscriber with immediate updating, and queued updating as failover.
I configured the publisher and subscriber. The snapshot replicates fine to the subscriber and all the agents are working fine. There is only 1 table article configured for replication.
Let's say I am trying to update a single row.
I can make as many updates on this row to the publisher as I want and they all replicate just fine to the subscriber. (Note: an update to publisher row causes a new GUID to be generated in the "msrepl_tran_version" column.) The updated data and the new GUID are successfully replicated to subscriber. I can continue to successfully make as many updates to this row on the publisher just fine.
Now I want to make an update to this row at the subscriber: When I do this (via enterprise manager), I update column 1 with a new value, but a new GUID is NOT created on the subscriber. The column I updated successfully replicates back to the publisher on the first update attempt. This update causes the publisher to create a new GUID for the row, but the new GUID does not replicate back to the subscriber. (at this point the publisher and subscriber do not have matching GUID values).
Further updates to this row on the subscriber cause an error "...rows do not match between publisher and subscriber...", and further updates to the publisher do not cause an error message, but simply does not update back to the subscriber.
Any help would be greatly appreciated!
Trans Replication Works Under certain conditions.
CBono
William Nordgren
Hi Rob,
When you make updates on subscriber, a new GUID should be created for msrepl_tran_version column. It is strange to me that a new GUID is not created when you make update on subscriber.
New GUID is created in the trigger with name like trg_MSsync_xxxx where xxx is your published table name. Could you try to trace this execution of this trigger when you make updates at subscriber
Thanks,
-Peng
awoomer
I actually just found out what was causing this problem this morning. The source table I was replicating from has a timestamp column (which happens to be named "timestamp"). The source tables I am replicating are the tables used by our CRM system, and the timestamp column is indirectly causing the problem. I can't delete this column because it is required by the CRM application.
When the transactional replication, and subscriber, were configured with immediate updating and queued updating as failover I was seeing these symptoms. When I disabled the queued updating subscribers and set to only allow immediate updates the replication and GUID's worked as expected.
When both update types were selected the trigger @ the subscriber was using the timestamp column, instead of the primary key column, in the WHERE clause to identify which row to update with a new GUID. Since timestamp columns update themselves automatically by SQL when the row is updated, the value that the trigger was using as a reference no longer existed in the table and was therefore not updating the row with a new GUID.
Changing the subscriber update type caused the "timestmp" named column to be a binary datatype at the subscriber and the trigger now uses the primary key column as a reference to update the GUID successfully.
I hope I explained this so that it makes sense, but I did find the solution. Thanks for your help! You were right that the problem was in the triggers, but indirectly caused by SQL itself.
Shenry
Hi Rob,
I am glad you fingure out the problem. But I am still not sure why trigger use the timestamp column to identify the row to update with a new GUID. In your published table, do you have any constraint on timestamp column, such as primary key, unique constraint, etc
thanks,
Peng