IDENTITY_INSERT is set to off error IDENTITY_INSERT is set to off error sql sql

IDENTITY_INSERT is set to off error

It would seem that your code is trying to insert a specific value into the primary key column that is defined as IDENTITY.

To avoid the error - do not insert any values! Let the database handle getting a new value for the row you're inserting.

If you're using Linq-to-SQL, click on the table in question in your visual designer, and have a look at the properties:

alt text

The "Auto Generated Value" must be "True" (which is not the default), and the Auto-Sync should be "OnInsert" (again: not the default). Also, set the "Primary Key" to true, and check to make sure the "Server Data Type" is correct - Int NOT NULL IDENTITY (or something like that).

If you ever need to override the default behavior (typically only in clean-up scripts or one-off data manipulations), you can turn ON the IDENTITY_INSERT on a table, e.g. after that, you can insert any value you like into the IDENTITY column:


This is more of a cleanup / data admin kind of task - not something you should do all the time as a dev.

You are trying to set a primary key value explicitly probably in the LINQ entity possibly; you are trying to say, for example, insert 1 in the primary key field that's an identity, and LINQ sees this as an inserted value.

Also if using LINQ to SQL, in the server data type of the field, ensure that it says IDENTITY in the data type field; if it doesn't say IDENTITY, there may be a configuration error in LINQ.

Thank you all for your suggestions, I ended up solving my issue by doing a refresh of the model. My model had been created before I set the primary key to the Identity spec with auto-increment, so I guess all it needed was a refresh to get it going.