Updateable Subscriptions: Rows do not match between Publisher and Subscriber

Question

I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database and the application which uses the database. However, now I run into issues when I try updating a section in my application. It does not go through the with update and gives the following error:

Execption Message: Updateable Subscriptions: Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber. Updateable Subscriptions: Rolling back transaction. The transaction ended in the trigger. The batch has been aborted. Exception Triggered by Method: OnError

The update statement obviously doesn't go through. However, when I try it the second time it works. Replication is working. Everything seems to be replication.

Can anyone explain why this error would occur and how I can resolve this issue. I would really appreciate the help!...

Answer 1

Several years ago Hilary Cotter responded to a similar problem. In his post he mentioned that synchronous replication is more reliable than asynchronous replication.

Rows Do Not Match Between Publisher and Subscriber

Part of Hilary's response is included:

Your chances of having this work successfully would be better if you did a sync subscription (i.e. the subscriber does not have the schema and data in place option).

I would advise you to drop and recreate the subscription and push it again, again making sure that nobody else was working on the publication database while you are doing this.

The poster of the question responded to this direction with:

I have since dropped and re-created the table at the subscriber that wasn't updating properly and this seems to have cured the problem. ... I can only guess that something had gone awry in the system tables regarding this object.

The poster also noted that that he was using asynchronous replication and noted that it was extra work to tear down and recreate the affected referential integrity.

Nonetheless, that apparently got his replication running again.

Answer 2

Maybe it is because of the replication latency, i.e. when you first run, the pub and sub tables are not sync'ed and during your next run, the tables are sync'ed and the 2nd run succeeded.