Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Right now I'm having an "identity crisis" with transactional replication in SQL Server 2008 R2. The database is running in compatibility 90. There's a table that has an identity column and won't replicate over. The error is "Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)".

The "not for replication" is set to true for that table. I can't find any settings for the articles to specify this as well.

Any ideas are appreciated.

share|improve this question

Just to add that you don't have to drop and re-create the replication just to change the "not for replication" bit.

You can do it using T-SQL without generating a snapshot or breaking your replication --


1 = not for replication

0 = for replication and this causes the problems with Identity colums on subscriber side

To change it for all the tables :

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

To change it for just 1 table, first find out the object_id of the table and then run below

EXEC sys.sp_identitycolumnforreplication table_object_id, 1
share|improve this answer
I'm not sure if it's worth-mentioning that we need to apply this to both DBs, Publisher and Subscriber. Thanks by the way, your answer saved us precious time. – eradicus Sep 12 '14 at 8:48

Make the triggers on the subscriber not for replication and ensure that the identity column on the subscriber is marked as not for replication. This should take care of the problem.

share|improve this answer
Thanks! I made sure it was set to "not for replication". I ended up dropping and re-creating the replication. It now works. Go figure. – user728263 Aug 1 '12 at 19:37
If I answered your question, please mark as answer. If my post was helpful, please mark as helpful. many thanks :D – user9399 Aug 1 '12 at 19:49

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.