Microsoft KB Archive/305127

= PRB: How to Generate a Snapshot of a Published Table and Apply It to the Subscribers =

Article ID: 305127

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Developer Edition

-



This article was previously published under Q305127



SYMPTOMS
SQL Server 2000 enables replication of declared referential integrity (DRI) to Subscribers through the initial snapshot. The addition of this feature represents a significant performance enhancement for SQL Server 2000 as compared to other versions, but may cause unexpected results when the snapshot is applied to a Subscriber. If you attempt to make schema modifications when the snapshot is applied, synchronization fails with one or both of the following error messages:

4929: Cannot alter the %S_MSG '%.*ls' because it is being published for replication.

3727: Could not drop constraint. See previous errors.

This article explains how to apply a snapshot to a published table to resynchronize data without modifying the schema.



CAUSE
When the SQL Server 2000 Snapshot Agent drops and re-creates or truncates a table, it drops all constraints on that table first. This behavior is by design and is implemented by a call to the sp_MSdroparticlecontraints stored procedure, which takes a target table and owner as parameters and iterates through all constraints in that table. The sp_MSdroparticlecontraints stored procedure drops Primary and Foreign keys, and Unique and Check constraints.

If a published article exists on the Subscriber, you can select one of the following options on the Snapshot tab:
 * Keep the existing table unchanged.
 * Drop the existing table and recreate it.
 * Delete data in the existing table that matches the row filter statement.
 * Delete all data in the existing table.

To select an option in the Publications Properties dialog box click Articles, click Article ellipsis (...), and then click Snapshot.

With any selection other than Keep existing..., the publication snapshot folder contains a file named Snapshot.pre. Snapshot.ext calls the sp_MSdroparticlecontraints stored procedure in the master database and attempts to drop constraints on the target table. If, for any reason, a constraint cannot be dropped, the Snapshot Agent and data synchronization fails.



WORKAROUND
To work around the problem and prevent the error message, select the Keep the existing table unchanged option. The Keep the existing table unchanged option prevents the Snapshot Agent from creating the Snapshot.pre file. You can then run a script against the target table prior to snapshot application as described in the &quot;Executing Scripts Before and After the Snapshot is Applied&quot; topic in SQL Server 2000 Books Online. This is a convenient feature because the Snapshot Agent automatically performs whatever data manipulation is specified prior to synchronization.

Additional query words: REPL replication snapshot DRI

Keywords: kbprb KB305127

-

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

© Microsoft Corporation. All rights reserved.