Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 908711 - Last Review: November 19, 2005 - Revision: 1.3
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)
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)
is the name of the table on which you are trying to run an INSERT statement.
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.
To work around this issue, disable the
NOT FOR REPLICATION option for the IDENTITY column.
Microsoft has confirmed that this is a bug in the Microsoft products that are
listed in the "Applies to" section.
For more information, see the following topics in SQL Server
2005 Books Online:
- SQL Server Replication
- Replication Limitations
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
| kbsql2005engine kbbug KB908711 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate