Transactional Replication Issues

By Chris Miller on 14 September 2000 | Tags: Replication

Justin is having some issues doing updates to replicated databases using Transactional Replication with Immediate Updating Subscribers. Click "read more" for details.

We have 3 servers, call them SQL1, SQL2, and SQL3.

SQL1 is a publisher, running NT4sp6a/SQL7Sp1, publishing two tables - call them pub1 and pub2

SQL2 is a distributor for SQL1's publications
SQL2 is also a subscriber to the publications pub1 and pub2 on SQL1
SQL3 is a subscriber to the publications pub1 and pub2 on SQL1

Transactional Replication with Immediate Updating Subscribers is used to replicate pub1/pub2 to SQL2 Transactional Replication with Immediate Updating Subscribers is used to replicate pub1/pub2 to SQL3

When updating records from SQL2 or 3, we intermittantly get the following error that doesn't clear until we stop and restart the synchronisation on the db affected (Client is a web server running IIS4 utilising ASP/ADO (and Select/Updates) to modify data in the database)

The SQL error we're seeing is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Updatable Subscribers: Rows do not match between Publisher and Subscriber. Refresh rows at Subscriber.
/weborder.asp, line 134

The line in question is the rs.execute statement which sends an update through to the database. I can send you the text of the update if you like."

First, here's a knowledge base article to read.

Second, here's your architecture as I understand it:

SQL1 is the publisher
SQL2 is the distributor and is a subscriber
SQL3 is a subscriber

All replication is transactional replication, and it's all one-way (transactions are going from SQL1 to SQL2 and SQL3).

The error you're getting occurs when the updates to the publication are being applied at the subscriber simultaneously with an update from your web client. So you have a couple options you can use:

1. Since you're working with immediate updating subscriptions, select data from SQL2/SQL3 and update only to SQL1. The data will propagate out to SQL2/SQL3. That way your replication isn't stepping on your updates.

2. Change your architecture. If your architecture is the way it is because you have some data that is largely static on SQL1 and is published to 2 and 3, and the updates on 2 and 3 are to a different set of tables, then partition the data into a database that gets published out for the static tables and a database that is updated for the ones that change. That's probably the best solution, but it only works if you can architect your application that way.

Partition the data if you can, otherwise just make sure you send your updates to the publisher and let them filter down.

Also, another pointer. Don't use Select and Update in your ASP. It's cleaner to write stored procedures and call the stored procedures and let them do the select and update for you. That's a whole separate article, though.

rocketscientist.