Sql-server – Strange UPDATE results in presence of merge replication

merge-replicationms accessreplicationsql serversql-server-2016

I have an MS Access front-end database that accesses an SQL Server backend. This (as far as we can tell) works flawlessly when the SQL Server database is not replicated. Once we introduce replication (from SQL Server Standard on a proper server as the publisher to SQL Server Express on a laptop as a subscriber using Merge Replication) we start getting weird database corruption that I can't explain.

What happens is that we have UPDATE queries which run against several tables in the database, and we also have Access itself doing CRUD operations against multiple tables as well (this corruption is not limited to one table). The result we see is that randomly (about 1 in 10 operations) a row other than the one we wanted to update is being updated, which overwrites data we didn't want to overwrite.

The replication, when set up, runs on demand on the laptop as a pull merge, and the corruption happens regardless of whether replication is performed. It only has to be enabled. No corruption seems to occur when replication isn't enabled.

I'm not accusing Microsoft of any fault here – it's entirely probable I've just forgotten to tick some box to prevent this. I'm just not sure what it is that I need to look for.

Edit: What I mean by corruption is this: Let's say I have rows:

ID | FirstName | LastName
--------------------------
1  | John      | Smith
2  | Emma      | Citizen
3  | Bob       | May

I then run something along the lines of:

UPDATE Table SET FirstName = "Test" WHERE ID = 1

And after that happens I end up with this:

ID | FirstName | LastName
--------------------------
1  | Test      | Smith
2  | Test      | Citizen
3  | Bob       | May

There are no error messages in any of the system tables dealing with replication. The only change in the schema is that when replication is enabled it creates the rowguid column.

Best Answer

So, it turns out (after hours upon hours of trying to diagnose this) that I did have an issue caused by merge replication being enabled, just not the one I thought I did. For future reference, here's what was happening...

I had a DAO query written in VBA that looked like this:

Set rs = CurrentDb.OpenRecordset("tblExample", dbOpenDynaset, dbSeeChanges)
rs.AddNew

rs.FirstName = "John"
rs.LastName = "Citizen"

rs.Update

rs.Move 0, rs.LastModified
txtPrimaryKey.Value = rs!PrimaryKeyID

rs.Close

The issue was the rs.Move 0, rs.LastModified line, which moves to the most recently updated row (the one just inserted) and then the next line retrieves the new primary key. This had multiple issues:

  • If two users were inserting records at exactly the same time, this would retrieve the same primary key for both users.
  • And of course, the triggers that run after an insert to do merge replication also change things (in other tables) and so this again returns a primary key for what the replication was doing, not what we were doing.

Basically, this code works for single-user databases (which is where it originated) but doesn't work for multi-user scenarios.

The solution (for now) is something along the lines of this, manually retrieving the correct row using all the values just inserted:

txtPrimaryKey.Value = DLookup("PrimaryKeyID", "tblExample", "FirstName=""John"" AND LastName=""Citizen""")

This has other potential issues (if you have two rows with exactly the same data, you'll probably get back the wrong primary key ID) but in this case, for this table, that isn't an issue.

Related Question