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

If you are facing this error "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" when you are running transaction replications, here is the reason behind this.

You are trying to insert a row in the secondary server where the table property "NOT FOR REPLICATION" option is enabled.
To work around this issue, disable the NOT FOR REPLICATION option for the IDENTITY column.
Here is how we can check the value:

The "Not For Replication" setting for identity columns allows replication to pass the ID value from the publisher to the subscriber without the subscriber creating a new ID.  Since identity columns will always generate a new number when records are inserted, this setting allows you to ensure the values on both your publisher and subscribers stay in synch.
This option can be set when designing or creating a new table as shown below in the highlighted section.
Right click on the table in the secondary server, click on Design in context menu,
check in the properties whether the "NOT FOR REPLICATION" property is set to "yes".

 

If this value is set to "yes" change it to "no" using the below script

declare @objid int
select @objid = object_id('Adventureworks')
exec sys.sp_identitycolumnforreplication @objid, 0

The above query will set the "NOT FOR REPLICATION" property to "No". Once you set the value you need to perform the reinitialize the replication to get all the old values.

Ref. MSDN Article: http://support2.microsoft.com/kb/908711
http://www.mssqltips.com/sqlservertip/1274/change-not-for-replication-value-for-sql-server-identity-columns/

0