SQL Server 2000 SP4 Upgrade Deleted Column Non-Convergence
Sometimes the simplest solutions are the best. This was certainly the case shortly after an upgrade from SQL2000 SP3 to SP4 started creating non convergence on a large table with significant numbers of deleted / added columns. On closer inspection the problem only affected the publisher on updates, where the field next to the target field was being updated when the change reached the subscribers.
Publisher insert/delete and subscriber insert/delete/update worked fine.
Either colv1 was being updated incorrectly by the merge trigger (most likely an error in @missingbm) or the stored procedure that applies the update was wrong. Either way it was a major problem.
I thought that adding a new dummy field might ‘throw a spanner in the works’ so I tried it on the test system (an exact restore to a computer of the same name to make sure the deleted columns were still ‘ghosts’ in the merge tables) and amazingly the problem was fixed.
I applied the ‘fix’ to the production server and that was fixed as well. Sometimes it pays to try the simple things first.
Publisher insert/delete and subscriber insert/delete/update worked fine.
Either colv1 was being updated incorrectly by the merge trigger (most likely an error in @missingbm) or the stored procedure that applies the update was wrong. Either way it was a major problem.
I thought that adding a new dummy field might ‘throw a spanner in the works’ so I tried it on the test system (an exact restore to a computer of the same name to make sure the deleted columns were still ‘ghosts’ in the merge tables) and amazingly the problem was fixed.
I applied the ‘fix’ to the production server and that was fixed as well. Sometimes it pays to try the simple things first.

Comments