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.