SQL Server replication error

MS SQL Server replication uses database constraints on primary key columns to prevent collisions between servers.

For example, let's say you have a table called "users" with a primary key called "user_id" running on server A and server B. Server A will have a constraint on the user_id column permitting values in the range 1 to 100. Server B will have a constraint on the user_id column permitting values in the range 101 to 200. This way, if a new user is inserted at the same time on server A, and meanwhile a different new user is inserted on server B, they will never have the same user_id number.

Since you broke replication intentionally and don't plan to start it up again, you have two options. (Standard disclaimer: make a backup.)

If you haven't already, you could try deleting the publication from within SQL Server Management Studio. That should remove all of the replication configuration stuff from your database and may fix the issue for you.

Otherwise, if you need to do it manually, you should be OK to go in and delete these constraints by hand. Go to the table in question, drill down to constraints, and look for one(s) named something similar to "repl_identity_range". Right-click on it and select "Modify". You should see an expression limiting the identity range. It will look something like this:

([user_id]]>(1) AND [user_id]]<=(100))

This is the actual rule that makes sure that newly generated ids are unique to each server. Delete it and you should stop seeing these errors.

Here are some other resources that might be helpful/informational:

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/ http://msdn.microsoft.com/en-us/library/ms176057.aspx