我IDENTITY_INSERT设置为ON，但我得到一个SQLEXCEPTION说这是关 [英] I set IDENTITY_INSERT to ON but I get a SqlException saying it's OFF

问题描述

我想从一个文本文件中抓取数以千计的发票（和其他的东西），并把它们插入到SQL Server 2008中我写了一个小控制台应用程序做到这一点，它使用LINQ to SQL。我插入现有的所有发票，我想 INVOICE_ID 是一个标识列和自动递增，所以我把它设计成这样的：

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：无法插入的表'发票'标识列显式值时IDENTITY_INSERT设置为OFF时

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

我跑SQL事件探查器，我可以看到它确实设置 IDENTITY_INSERT 到 ON 尝试执行之前，插入。我没有看到它的任何地方被设置为关闭。我是pretty的新的SQL事件探查器，所以也许有一个事件我可以启用，将提供给我更多的信息，试图调试这一点。有任何想法吗？

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?

我试过在多数民众赞成使用LINQ到SQL的.dbml文件调整值。我有发票表中的自动生成的值设置为假，自动同步设置为从不，而服务器数据类型设置为int NOT NULL。一般情况下我将其设置为真，在插入和内部NOT NULL标识，分别，但是当我做，我可以看到，SQL Server正在excecuting：

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:

选择转换（智力，SCOPE_IDENTITY（））AS [值]

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

而不是插入的 INVOICE_ID ，我提供的。

rather than inserting the Invoice_ID that I provide.

推荐答案

HTTP： //msdn.microsoft.com/en-us/library/ms190356.aspx

如果你往下看，可以看到：

If you look down, you can see:

如果一个SET语句运行在存储   过程或触发器，值了   控制之后SET选项恢复   从存储过程返回   或触发。此外，如果一个SET语句   在一个动态的SQL字符串指定   这是通过使用运行   对sp_executesql或EXECUTE，值   之后，SET选项恢复   控制从批次返回   在动态SQL字符串中指定。

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.

这篇关于我IDENTITY_INSERT设置为ON，但我得到一个SQLEXCEPTION说这是关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！