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.