Replication: XML data loss occurs when Merge agent fails with Violation of Foreign Key
Problem:
Consider the following scenario:
- You use a merge publication operation to replicate XML data in Microsoft SQL Server 2005, in Microsoft SQL Server 2008, or in Microsoft SQL Server 2008 R2.
- A foreign key violation error occurs in the same merge replication batch.
In this scenario, you may find the merge replication fails and the XML data is partly replicated or lost. Additionally, an error message that resembles the following is logged in the SQL Server error log if this issue occurs:
The merge process could not replicate one or more Insert statements to the Publisher. A Stored Procedure failed to execute.
Cause:
This issue occurs because the merge replication stays in an error state and cannot handle a sequential stream of the XML data when a Foreign Key violation error occurs.
Solution: