“Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF. “

In yesterdays post I mused what surprises replication would have for me after resolving an issue around the error message “Could not execute sp_replcmds”. Today’s replication issue comes from an error message that greeted me this morning:

“Cannot insert explicit value for identity column in table [table] when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544”

Although no changes had occurred last night, it seemed that a table that had been published sometime ago was empty until part of the application inserted into the table and the commands had attempted to be distributed out. Typically, and identity columns should be marked as “NOT FOR REPLICATION” in the create table script:


CREATE TABLE authors 
( COL1 INT IDENTITY (1, 1) 
NOT FOR REPLICATION PRIMARY KEY )

So I scripted out the affected table on the publisher database and was surprised to see that this was already set. So I double checked the subscriber databases and was equally surprised to see that “NOT FOR REPLICATION” was NOT set. It’s difficult to ascertain why this was the case; maybe someone had added it to the publication without the “NOT FOR REPLICATION” setting, created a snapshot/re-initialised and then realised their mistake and update the publisher, not realising that this change would not be pushed out. The easiest way to change this setting is to run the following stored procedure:


  declare @int int
set @int =object_id('authors')
EXEC sys.sp_identitycolumnforreplication @int, 1

But this stored procedure needs to be run at both the publisher database AND the subscriber database. So I ran the stored proc on the subscribers and checked the distribution log for each subscriber and the error was resolved: there’s no need to send out snapshot or reinitialise the publication.

The stored procedure can also be used to mark a tables identity columns for replication, by specifying 0 instead of 1.

I wonder if replication can make it three surprises in a row? Come back tomorrow to find out!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s