7

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.

7

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 :

enter image description here

  • 1
    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. – setzamora Sep 12 '14 at 8:48
  • 1
    Kin to the rescue again! In my case, sp_msforeachtable wasn't readily available (Azure) without a custom implementation. So I just copied all of the ObjectID's from the last query and did some multi-line editing in Sublime. – pimbrouwers Oct 17 '17 at 17:12
3

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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