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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: