Heads-up on another Merge Replication Bug in SQL Server 2005 involving Dummy Updates

Discovered by Patrick Molijn and investigated by Frank Schaafsma

This is another quick article provided by Patrick Molijn who has discovered another worrying bug in SQL Server 2005:

In order to synchronise two merge replicated databases(non-convergence) we use the sp_mergedummyupdate stored procedure.  It appears that this sp is missing an essential check!

I asked a colleague (Frank Schaafsma) to dig deeper into the matter and this is the outcome:

sp_mergedummyupdate checks (for tables where 0=@islightweight) whether to call sp_MSdummyupdate with metadata_type = 1 (tombstone), metadata_type = 2 (contents) or metadata_type = 3 (contents_deferred).

The decision between metadata_type 2 or 3 is made based on the existence of ‘select rowguid from dbo.MSmerge_contents where rowguid = ''' @rowguidstr + '''.

In our cases this is not correct, because there are multiple tables with the same rowguid (for a 1:1 relationship with both tables the rowguid as primary key). So the existence check finds a record in MSmerge_contents, but for a different table.

The existence check should probably include ‘and tablenick = ' + @tablenickstr’ to be correct.