FIX: Fkey NOT FOR REPLICATION Doesn't Work Correctly

ID: Q148819

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

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." For additional information, please see the following article in the Microsoft Knowledge Base:

Q135871 : INF: Update Methods Used in SQL Server 6.0

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 : kbprg SSrvRep kbbug6.50 kbbug6.00 kbfix6.50.sp2
Version : 6.0 6.5
Platform : WINDOWS
Issue type :