Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Question

I am trying to import records from an old database into a new database clean database. I have setup the new database to have IDENTITY Primary keys, however I want to insert the same values as the original database.

in SQL I did :

set IDENTITY_INSERT plans on

I am using LINQ to SQL and I have turned off Auto-generated columns but I am still getting this error:

Cannot insert explicit value for identity column in table 'Plans' when IDENTITY_INSERT is set to OFF.

but it IS ON

Answer 1

The identity_insert setting is local to the database session. You have to use the same database connection when you set it, as when you do the insert.