BUG: REPL: Reference Constraints Are Not Getting Applied to Filtered Subscriptions

• Microsoft SQL Server version 7.0

SYMPTOMS

After setting up merge publications with static filters, the constraints (relationships) between tables may not be transferred to the subscriber. As a result, you may not be able to maintain the referential integrity on the subscriber.

This can occur in SQL/SQL merge and Jet/SQL merge replication.

CAUSE

This problem occurs if the publication does not also include the tables referenced by the foreign keys of the tables that are included. SQL Server does not script any references if ANY of the referenced tables are not included in the publication.

WORKAROUND

To work around this problem, perform the following steps:

1. Subscribe to a statically filtered publication and let SQL Server do the initial data synchronization. Note that the constraints won't be applied.



2. Script all the constraints on the Publisher and apply them on the Subscriber. For information on how to script constraints see "How to generate a script (Enterprise Manager)" in SQL Server 7.0 Books Online.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

Using Data Transformation Services (DTS) to copy all schema, constraints, and data to the subscriber and then set up the subscription with the No, The Subscriber Already Has The Schema And Data option does not work around this problem. This is because when you choose not to synchronize the databases, the Merge Agent drops all constraints when it starts.

Additional query words: repl

Keywords : kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug