SQL Server Merge Replication Error: The schema script could not be propagated to the subscriber

SQL Server Merge Replication is a powerful solution for synchronising data between SQL Server databases. It is useful for working offline, for site-to-site data synchronisation, and for backup system scenarios.

However, when replication goes wrong, resolving the issue at hand can prove to be challenging. The number of moving parts means that every time you encounter a replication issue, often it will be one that you’ve never seen before.

In the sections below I cover two different solutions to a specific replication problem I came across recently.

The problem

Recently one of my colleagues reported the following replication error which was returned by the Replication Merge Agent while attempting to synchronise with the server.

The schema script could not be propagated to the subscriber.

The Replication Merge Agent runs on the subscriber. It merges data to and from the main publisher database and it applies any required schema migrations. A large proportion of replication errors tend to occur whenever the Replication Merge Agent is attempting to apply schema changes that have been made at the publisher.

This particular issue occurred for an existing replication setup, even though no one was aware of any schema changes being made. The problem was unusual in that the Replication Merge Agent was attempting to add a SQL Constraint twice in the same replication cycle.

Here is the full error message for reference.

The schema script ‘if object_id(N'[dbo].[<table_name>]’) is not null exec(‘ALTER TABLE [dbo].[<table_name>] ADD CONSTRAINT
DF_<table_name>_<column_name> DEFAULT ”” FOR <column_name>
‘)’ could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Column already has a DEFAULT bound to it. (Source: MSSQLServer, Error number: 1781)
Get help: http://help/1781

Note that if you have encountered a similar issue, the ‘<table_name>’ and ‘<column_name>’ text within the error message will, of course, vary depending on the database.

Now that we’ve identified the problem, let’s look at potential solutions.

Solutions

Initially, the most obvious solution would appear to be removing the constraint from the subscriber database and then running the Replication Merge Agent again.

However, removing the constraint and trying to replicate again simply resulted in the constraint being added back into the subscriber database, followed by the same error reappearing shortly afterwards.

If you are finding the same thing is occurring for you at this point, you’ll need to try one of the following solutions instead.

Solution #1

In order to resolve the issue, we need to track down the source of the schema change and then find a way to prevent it from being applied so that we can bypass the cause of the error.

Replication schema changes are stored in the ‘sysmergeschemachange’ system table within the replicated database.

Rather than querying the ‘sysmergeschemachange’ system table directly, you can execute the following SQL on the publisher to enumerate all pending schema changes for the relevant publication.

EXEC sp_enumeratependingschemachanges
@publication = 'PUBLICATION_NAME'

Note that you must change the 'PUBLICATION_NAME' text to the name of your publication before executing the above SQL.

After executing the Stored Procedure, you should see a list of schema changes.

You can find more information about the sp_enumeratependingschemachanges Stored Procedure in the Microsoft Docs.

In order to avoid the need to reinitialise the subscription (and potentially lose data), you can mark the schema change as ‘skipped’ in order to bypass it, as follows.

EXEC sp_markpendingschemachange
@publication   = 'PUBLICATION_NAME',
@schemaversion = 121,
@status        = 'skipped'

Note that you must change the 'PUBLICATION_NAME' text to the name of your publication and update the @schemaversion parameter value to match the schema change version before executing the above SQL.

Now if you rerun the Replication Merge Agent, you should find that the schema change will be skipped and if all goes well the data merge will complete successfully.

Solution #2

If the above approach does not work for whatever reason, as a last resort you can opt to remove the schema change record completely.

First of all, run the following SQL to find the offending schema change record.

SELECT   pubid, artid, schemaversion, schemaguid, schematype, schematext, schemastatus, schemasubtype
FROM     sysmergeschemachange
WHERE    schematext LIKE '%SEARCH_PATTERN%'
ORDER BY schematext

Note that you must change the '%SEARCH_PATTERN%' text with the name, or partial name, of the constraint/object that you are looking for.

After identifying the schema change record which is causing the issue, remove the row from the table in both the publisher and subscriber databases and then run replication again.

You must be very careful to ensure that you have identified the correct schema change row before proceeding with the deletion to avoid the risk of breaking your replication setup.

Now try rerunning the Replication Merge Agent and if all goes well this time, the schema change will be ignored and the replication cycle will complete successfully.

Summary

In this article, I have shown you two approaches for resolving the ‘The schema script could not be propagated to the subscriber’ replication error.

Replication can be somewhat frustrating whenever issues like this arise, so it is always a welcome relief whenever you come across a working solution for the exact issue you are facing.

Let me know in the comments if either of the solutions worked for you!