Replication: The schema script could not be propagated

  • Article

Problem:

I create a SQL Server 2005 replication topology on my Windows 2003 server. Everything synchronizes fine.

I then go and add a default constraint to an article.

Subscriber is synchronized and the default constraint is propagated to the subscriber.

Subscriber is marked for reinit with upload first.

When the subscriber is marked for re-initialisation, the schema version will be rolled back to 0. If the upload_first option is used, the merge agent retrieves schema changes from sysmergeschemachanges and tries to apply them at the subscriber before uploading the data. Therefore it attempts to create the constraint again which already exists at the subscriber.

Workaround available:

To avoid data loss at the subscriber, you have to manually resolve the reason for the failure at the subscriber side:

WORKAROUNDS:

1- Drop the DEFAULT constraint on the subscriber before applying the snapshot:
ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>

Note: you will have to do this after each reinit on each subscriber

2- Use sp_reinitmergesubscription ... @upload_first = 'false'