SQLServerWiki

“The Only Thing That Is Constant Is Change”

How to drop Replication Identity Check Constraint.

Posted by database-wiki on April 11, 2016

Problem Description:

Customer executed sp_changemergearticle to disable Auto Identity Range Management.
One subscriber still had the Identity Check Constraint.

When try to manually remove check constraint get the following error

–TESTING
ALTER TABLE [dbo].[Shippers] DROP CONSTRAINT
[repl_identity_range_259EDBE5_AC01_41C1_ADDB_997EA2098FB2]

Msg 25010, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 284
The constraint is used by merge replication for identity management and cannot be
dropped directly. Execute sp_changemergearticle @publication, @article,
“identityrangemanagementoption”, “none” to disable merge identity management, which
will also drop the constraint.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 182
The schema change failed during execution of an internal replication procedure. For
corrective action, see the other error messages that accompany this error
message.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Resolution:

Untested and Unsupported Resolution

DISABLE TRIGGER [MSmerge_tr_altertable] ON DATABASE
GO

ALTER TABLE [dbo].[Shippers] DROP CONSTRAINT
[repl_identity_range_259EDBE5_AC01_41C1_ADDB_997EA2098FB2]
GO

ENABLE TRIGGER [MSmerge_tr_altertable] ON DATABASE
GO

Leave a comment