Share via

Explicit value must be specified for identity column in table [XXXXXX] either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column

  • Article

I encountered this error when explicitly inserting values in an Identity column of a Table that is created in a SQL Server 2008 Database and the DB is not part of any kind of Replication. I have made sure that "Identity_Insert" is turned on for that table before performing the insert. Also I have checked that "NOT FOR REPLICATION" is disabled for that Identity column (Obviously, it is in disabled state in my case as there is no Replication).

After struggling for some time, I noticed that an "Instead of Insert" Trigger exist on the Table. The interesting thing is, no change is happening to the Idenity column in the Trigger definition. However, disabling that Trigger fixed the issue miraculously :-)

Let me know if this fix has helped you too.

Comments

  • Anonymous
    August 14, 2013
    Thanks for making me think about it that way. Of course, the instead of trigger does not include the identity field, because it is for when identity insert is OFF.