Microsoft KB Archive/240192

Knowledge Base

FIX: Unresolved Merge Conflicts With Constraints And Triggers

Article ID: 240192

Article Last Modified on 3/14/2006

---

APPLIES TO

• Microsoft SQL Server 7.0 Standard Edition

---

This article was previously published under Q240192

SYMPTOMS

When merge replication is established on tables that have constraints, namely, foreign key constraints or user triggers and these triggers/ constraints are not created with "NOT FOR REPLICATION" option, merge agent will log a conflict. For example, if the conflict is on a delete statement, the message may look like:

'DELETE' statement conflicted with column reference constraint 'constraintname'.
The conflict occurred in database 'databasename', table 'tablename',column 'columnname'

The error described above occurred when trying to delete this data at this
location, possibly because the deletion violated one or more constraints.
If you choose to ignore this conflict, you should resolve it through other
means. Consider logging the details of this conflict, then sending the log
entry to your system administrator. The value of the unique ID in column
'rowguid10' is '{31D07D7A-50EA-11D3-AD71-0050041B87D2}'.

The conflict viewer gives you an option to "ignore the conflict". Even if you try to choose "ignore conflict" and resolve the conflict, the conflict is not resolved. In other words, the conflict entry is not removed from MSmerge_delete_conflict (in case of delete conflict) or conflict_tablename (in case of insert or update conflicts) on the publisher.

When constraints or user triggers need to be enforced on tables involved in merge replication for business reasons, it is recommended that constraints/user triggers be marked as "NOT FOR REPLICATION" to avoid the above problem. If these constraints/triggers are not marked as "NOT FOR REPLICATION", the conflicts like the ones described above have to be resolved manually by removing the entry from the conflict tables MSmerge_delete_conflict (in case of delete conflict) or conflict_tablename (in case of insert or update conflicts) on the publisher database for the given rowguid.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

Conflict viewer does not resolve these conflicts, fearing breakage of database integrity rules.

Here is an example of typical scenario where conflict viewer cannot resolve conflicts:

1. Create two tables on the publisher:

   create table t1(c1 int primary key, c2 uniqueidentifier rowguidcol)
   create table t2(c1 int primary key, c2 int foreign key references t1(c1),
   c3 uniqueidentifier rowguidcol).
                       

2. Insert the following rows into table t1 and t2:

   insert into t1 values(1,newid())
   insert into t2 values(1,1,newid())
                       

3. Create a merge publication consisting of table t1 as an article.
4. Subscribe to the publication.
5. Run the snapshot and merge agents to synchronize the data.

6. Delete the following row from subscriber:

   delete from t1 where c1 =1
                       

7. Run the merge agent now and the delete done in step 6 will cause the publisher to log a conflict, which will not be resolved automatically.

The conflict viewer does not resolve the conflict by choosing any combination of radio buttons.

Keywords: kbbug kbfix KB240192

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.