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

Question

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.

Derail

Answer 1

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.

Anhanhalt

Well darn. Could have sworn I used to have it working, but clearly I'm mistaken. Thanks for the help! :) – Derail

Answer 2

While the scope of the IDENTITY_INSERT seems to be a problem, the solution I've found and tested in EF v4.1 is to open the connection first so that the session remains open. E.g.

using (SiteDataContext context = SiteDataContext.Create())
{
    context.Connection.Open();
    context.ExecuteStoreCommand("SET IDENTITY_INSERT [EnumValue] ON");

    var val = new EnumValue()
    {
        ID = 2000000,
        Value = "just a test",
    };
    context.AddToEnumValues(val);
    context.SaveChanges();

    context.ExecuteStoreCommand("SET IDENTITY_INSERT [EnumValue] OFF");
}

I tested this solution without calling context.Connection.Open(); first, and it failed as described. Then I added the line it worked perfectly.

Calumniation

Answer 3

I would expect SET IDENTITY_INSERT is scoped to the body of the procedure. The most common approach I've seen is to make a stored procedure which uses EXEC to execute dynamic SQL containing both the SET IDENTITY_INSERT and the INSERT itself.

Deplore