Search code examples
c#.netlinq-to-sqlidentityidentity-insert

I set IDENTITY_INSERT to ON but I get a SqlException saying it's OFF

I'm trying to grab thousands of invoices (and other stuff) from a text file and insert them into SQL Server 2008. I wrote a little console app to do this and it uses LINQ to SQL. After I insert all the existing invoices I want the Invoice_ID to be an identity column and auto-increment, so I have it designed as such:

CREATE TABLE [dbo].[Invoice]
(
 [Invoice_ID] int IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    /* Other fields elided */
)

Before I start inserting invoices I call a stored procedure that contains the following line:

SET IDENTITY_INSERT [dbo].[Invoice] ON;
/* Other lines for setup elided */

After I've submitted my changes I call another stored procedure with the following line:

SET IDENTITY_INSERT [dbo].[Invoice] OFF;
/* Other lines for clean up elided */

When I try inserting my invoices and submitting the changes I get the following exception:

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

I ran SQL Profiler and I can see that it is indeed setting IDENTITY_INSERT to ON before trying to perform the inserts. I do not see it being set to OFF anywhere. I'm pretty new to the SQL Profiler, so maybe there's an event I can enable that will provide me with more info to try and debug this. Any ideas?

I've tried tweaking values in the .dbml file that's used by LINQ to SQL. I have the Invoice table's Auto Generated Value set to "False", Auto-Sync set to "Never", and the Server Data Type set to "Int NOT NULL". Normally I'd have them set to "True", "On Insert", and "Int NOT NULL IDENTITY", respectively, but when I do I can see that SQL Server is excecuting:

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]

rather than inserting the Invoice_ID that I provide.

Solution

  • http://msdn.microsoft.com/en-us/library/ms190356.aspx

    If you look down, you can see:

    If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.