Microsoft KB Archive/908711

= 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 =

Article ID: 908711

Article Last Modified on 11/19/2005

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



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  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)

Note 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 &quot;Symptoms&quot; 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 &quot;Applies to&quot; section.

