SQL Server 2008 R2 Transactional Replication "Cannot insert explicit value for identity column..."

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.

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

EDIT

Below tsql will give you a nice output with command that can be reviewed before running for entire database :

if exists (select 1 from sys.identity_columns where is_not_for_replication = 0)
begin
SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) as SchemaName,  
        QUOTENAME(t.name) AS TableName, 
        c.name AS ColumnName,
        c.object_id as ObjectID,
        c.is_not_for_replication,
        'EXEC sys.sp_identitycolumnforreplication '+cast(c.object_id as varchar(20)) + ', 1 ;' as CommandTORun_SetIdendityNOTForReplication
    FROM    sys.identity_columns AS c 
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1
        and c.is_not_for_replication = 0
end
else 
print 'There are no identity columns that needs NOT FOR REPLICATION set to 1'

For adventureworks database :

StackExchange Database Administrators Q#21831, answer score: 8