0

Using SQL Server merge replication, one of the tables was accidentally unselected (check box unticked). How do I add it back in to the snapshot again? It was unfortunately a large table with plenty of child relationships.

There are 3 databases A - the publisher, B and C, the subscribers. There are a few rows that have been added on each, not many. I want to save all of them.

If I check the table again in the snapshot on server A and run it, then I get this error on server B:

Cannot drop table dbo.TableName because it is referenced by a foreign key constraint

I didn't want to drop it and reload it from scratch, I wanted to merge of course. Drop would lose the few rows that have been entered on B. And drop would affect the child tables too.

I understand that it is doing this because it thinks that the table is a new one in the snapshot, and the other server (B) already has a table with the same name, so it needs to be dropped first. I want it to merge instead.

So I changed the setting Action if name is in use to Keep existing object unchanged

But now it is trying to copy all rows from A to B, not just the changed ones so it errors with this on server B:

Violation of PRIMARY KEY constraint 'PK__TableName__id'.

How do I get out of this situation?

The @@version is Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64)

2
  • If there is no fixing within the replication mechanism... Could you (i) rename the table on B, (ii) get the whole 'new' one from A, (iii) merge the delta 'manually' once and be (iv) back to your routine? - Could work, if referential integrity were not enforced via DDL. Probably unlikely. -- So - if it has to be done: Save the delta to some 'staging' tables, re-instantiate the routine, merge in from the staging tables.
    – Abecee
    Jul 23 at 19:35
  • We ended up dropping B and recreating it from the subscription. Lost a few rows that had to be re-entered, but not too many luckily.
    – JK.
    Sep 22 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.