Skip to content

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON Error

October 29, 2012

Today as I made changes to my SQL database, I had seen this error  crop up all the time: –
“An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON”

If you had thought that before your SQL Statement you can write the following:
SET IDENTITY_INSERT <tablename> ON

Sometimes this does not work.

As SQL is expecting a row with containing a specific identity value into a table that contains an identity column, you may already have this in the design of this table.

But MS SQL can also throw this errr if the insert query is inserting more columns in the database table than it can hold,, as I had found out I forgot to add the extra data column to the table for all the tests I had applied for every machine. Adding the correct number of columns for the query works!!

 

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: