Microsoft KB Archive/148819

= FIX: Fkey NOT FOR REPLICATION Doesn't Work Correctly =

Article ID: 148819

Article Last Modified on 9/1/2006

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q148819



BUG #: 13700 (Windows NT: 6.5)



SYMPTOMS
If you set up a FOREIGN KEY constraint to reference a table that is populated by replication (that is, a subscribed table), a constraint violation may occur if an UPDATE is run against the published table. This will cause the distribution task to fail with the following error:

Msg 547, %s statement conflicted with %s constraint '%.*s'. The conflict

occurred in database '%.*s', table '%.*s'%s%.*s%s

The NOT FOR REPLICATION option should allow changes submitted by the Distribution task on columns referenced by a FOREIGN KEY constraint from another table that otherwise would violate the constraint.



WORKAROUND
Change your UPDATE statement to run as an "on-page delete/insert."

If you cannot change your UPDATE statement to meet this criteria, you may not be able to create a FOREIGN KEY constraint in the subscribed database to reference a subscribed table.



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.



MORE INFORMATION
This problem will occur if the UPDATE on the published table is processed as a "deferred" or "full delete/insert" UPDATE statement. For additional information about determining what type of UPDATE statement is being processed, please see the Microsoft Knowledge Base article listed above.

SQL Server Replication will generate all statements to be sent to subscribers based on what is recorded in the transaction log of the published database. In the case of a "deferred" or "full delete/insert" UPDATE statement, the transaction log contains a series of DELETE and INSERT statements combined into one logical transaction. The entries for these commands appear in the MSjob_commands table in the distribution database as a series of DELETE statements, followed by INSERT statements, depending on the number of rows affected by the UPDATE. For example, a deferred UPDATE that affects 100 rows will result in 200 rows in the MSjob_commands table for that job.

Please note that the Microsoft SQL Server Transact-SQL Reference guide (in the UPDATE statement section) states that an UPDATE can never be "in-place" against a published table. However, it is still possible for it to be an "on-page delete/insert," which is considered a "direct" update. For additional information, please see the Microsoft Knowledge Base article listed above.

Additional query words: sql6 rep kbfix6.50.sp2

Keywords: kbbug kbfix kbprogramming KB148819

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.