Merge replication throws PRIMARY KEY violation during the initialization of Subscriber

Question

I am using SQL Server 2014 SP1 to set up merge replication on Windows 2008. The databases (Publisher & Subscriber) are restored from the production back up of SQL Server 2005 publisher database.

The merge replication set up on SQL Server 2005 works fine. However when I set up the merge replication on SQL server 2014, I am getting the following error.

If I add new tables, there replication seems working fine. The issue is occurring only for the existing tables with production data.

Error messages:

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

The process could not bulk copy into table '"dbo"."FiscalVisitType"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

Batch send failed (Source: MSSQLServer, Error number: 0)

Violation of PRIMARY KEY constraint 'PK_FiscalVisitType'. Cannot insert duplicate key in object 'dbo.FiscalVisitType'. The duplicate key value is (1). (Source: MSSQLServer, Error number: 2627)

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)

bcp "SIPG_Subscriber"."dbo"."FiscalVisitType" in "C:\Program Files\Microsoft SQL Server\MSSQL12.OGIS\MSSQL\ReplData\unc\PC39606-ORG-D-P$OGIS_SIPG_PUBLISHER_OGIS_PUBLICATION\20150624142806\FiscalVisitType_2.bcp" -e "errorfile" -t"" -r"" -m10000 -SPC39606-ORG-D-P\OGIS -T -w (Source: MSSQLServer, Error number: 20253)

Answer 1

Have seen this on numerous occasions. Try these steps to resolve your issue.

1. Remove table from replication
2. Reinitialize subscription
3. Add table to replication
4. Reinitialize subscription

Answer 2

Since you restored your subscriber db from backup, what is your merge article property for that article, is it "drop table if existing"? or is it "keep table", or something like that. If it is the latter, that means data already exists, so when you do your initialization, the snapshot agent creates a bcp file from the publisher, and merge agent tries to bcp that file back into the subscriber table. so if your subscriber table has the same data, it will fail.

Answer 3

Thanks for the answers. I have resolved by restoring the Publisher using the production back up and then generated the database script (Tasks->Generate Scripts, with the option Schema only). The script was run against the Subscriber databases and then performed initialization with merge article property, Action if name is in use = Keep existing object unchanged. regards Bisaac