Sql-server – Merge replication throws PRIMARY KEY violation during the initialization of Subscriber

mergereplicationsql serversql server 2014

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)

Best Answer

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