SQL SERVER ERROR- Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF

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:

SQL SERVER ERROR- Cannot insert explicit value for identity column in table 'SchemeToLocation' when IDENTITY_INSERT is set to OFF

Resolution:

As we know that each replication consists of a publisher and subscribers. I executed the below query at the subscriber end and issue resolved.

Script:

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

From    sys.sysobjects

Join    sys.syscolumns

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.

Thank You

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration, Replication, Uncategorized. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s