Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

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 --

sys.sp_identitycolumnforreplication

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
add comment

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
1  
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
add comment

Your Answer

 
discard

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.