Search code examples
sql-serversql-server-2012replicationmerge-replication

How to skip a schema change in Merge Replication

How can I skip a schema change in Merge Replication?

I added a column to a table as not null, but subscribers first have to create the column with null data so its failing to replicate. I already changed this column in origin as null, but the agent want to synchronize first change first.

Advice?

Solution

  • You can locate the schema change you would like to skip in the table sysmergeschemachange. Another way to locate the schema change is to execute sp_enumeratependingschemachanges:

    EXEC sp_enumeratependingschemachanges 
    @publication = 'MyPublicationName'

    Get the schemaversion from the result set and then skip the schema change by executing sp_markpendingschemachange. For example, if the schemaversion is 22:

    EXEC sp_markpendingschemachange 
    @publication = 'MyPublicationName',
    @schemaversion = 22
    @status = 'skipped'