SQL SERVER ERROR- Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF
Yesterday I got this error while monitoring the replication status of a server. The actual error is in snapshot as below:
As we know that each replication consists of a publisher and subscribers. I executed the below query at the subscriber end and issue resolved.
Go to Subscriber end and execute the below query by changing the table name:
Declare @TableID Int = (
Select Top 1 sys.sysobjects.id As Table_ID
On syscolumns.id = sysobjects.id
Where sys.sysobjects.name = ‘Tablename’
And (sys.syscolumns.status & 0x88) = 0x80 — 0x80 = Identity, 0x08 = Not For Replication
Order By 1 )
IF @TableID IS NOT NULL
EXEC sys.sp_identitycolumnforreplication @TableID, 1
Note: After running this script at the subscriber end, go to publisher and right click on the database which we need to monitor and launch replication monitor and check the subscriber status.
Your likes and comments would be highly appreciated.