FIX: Fkey NOT FOR REPLICATION Doesn't Work CorrectlyLast reviewed: April 8, 1997Article ID: Q148819 |
The information in this article applies to:
SYMPTOMSIf 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%sThe 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.
WORKAROUNDChange your UPDATE statement to run as an "on-page delete/insert." For additional information, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q135871 TITLE : INF: Update Methods Used in SQL Server 6.0If 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.
STATUSMicrosoft 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 INFORMATIONThis 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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |