You receive an “Explicit value must be specified for identity column” error message when you use the replication feature to run an INSERT statement on a table in SQL Server 2005


Bug #: 20009838 (SQLBUDT)

Symptoms


Consider the following scenario. In Microsoft SQL Server 2005, you use the replication feature to run an INSERT statement on a table. You do this by using one of the following methods.

Method 1

You run the INSERT statement manually when the following conditions are true:
  • You do not specify the value of the IDENTITY column in the INSERT statement.
  • You run the INSERT statement during synchronization.
  • The table has an IDENTITY column for which the NOT FOR REPLICATION option is enabled.

Method 2

You run the INSERT statement in a trigger when the following conditions are true:
  • You do not specify the value of the IDENTITY column in the INSERT statement.
  • You run the INSERT statement during synchronization.
  • During synchronization, a replication agent modifies a table. The table that is being modified contains a trigger that inserts a record into a second table.
  • The second table has an IDENTITY column for which the NOT FOR REPLICATION option is enabled. Therefore, the INSERT statement in the trigger in the table that is being modified is run as a replication agent user.
When you use either of these methods, you receive the following error message when you try to use the replication feature to run the INSERT statement:
Explicit value must be specified for identity column in table
TableName either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
NoteTableName is the name of the table on which you are trying to run an INSERT statement.

Cause


This issue occurs because SQL Server 2005 cannot automatically generate a unique identity value for an IDENTITY column for which the
NOT FOR REPLICATION option is enabled. If you try to run an INSERT statement when the
NOT FOR REPLICATION option is enabled, you receive the error message that is mentioned in the "Symptoms" section.

Workaround


To work around this issue, disable the NOT FOR REPLICATION option for the IDENTITY column.

Status


Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

References


For more information, see the following topics in SQL Server 2005 Books Online:
  • SQL Server Replication
  • Replication Limitations