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.
Best Answer
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 :
To change it for just 1 table, first find out the object_id of the table and then run below
EDIT
Below tsql will give you a nice output with command that can be reviewed before running for entire database :
For adventureworks database :