插入记录时 SET QUOTED IDENTIFIER 应为 ON
我陷入了 SQL Server 2005 的一个相当奇怪的问题，它抛出
“插入记录时应启用 SET QUOTED IDENTIFIER”
（用作 SP）到特定表。 这之前工作得很好，但随机抛出这个错误。
我已经验证了SP。 我们没有在里面手动指定 SET QUOTED IDENTIFIER 设置，所以它必须默认为 ON。
有人可以澄清可能是什么问题吗？
该表必须使用 SET QUOTED IDENTIFIER ON 创建，对吗？ 我还没有检查表脚本。
我观察到这个问题仅发生在 SP 对日期列（modifiedAt）进行插入或更新时...示例值是“2009-08-10 06:43:59:447”..
是否有问题传递的值？
I am stuck in a rather strange problem with SQL Server 2005, which throws
"SET QUOTED IDENTIFIER should be on when inserting record"
(using as SP) to the particular table. This worked fine earlier but is throwing this error randomly.
I have verified the SP. We didn't manually specify SET QUOTED IDENTIFIER settings inside, so it must be ON by default.
Can someone clarify what could be the problem?
The table must be created with SET QUOTED IDENTIFIER ON right? I didn't check the table script yet.
I have observed that this problem only occur with the SPs doing insert or update on a date column (modifiedAt)... A sample value is '2009-08-10 06:43:59:447'..
Is there a problem with the values passed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
经过长期的努力，我们终于解决了这个问题。 我只是想分享一下原因。
我们的构建团队维护一个单独的内部工具来部署脚本，该工具在内部触发SQLCMD（shell）实用程序在数据库中执行T-SQL脚本。
罪魁祸首是：默认情况下，在 SQLCMD 模式下运行时，
QUOTED_IDENTIFIER为OFF！通过此工具运行的每个脚本都是使用
创建的引用标识符关闭。 我们是唯一使用索引视图的模块。 您在我之前的帖子中熟悉的所有其余故事:(注意：我将投票给每个人的帖子有用。
After a long struggle we were able to fix this problem. I just wanted to share the reason.
Our build team maintains a separate in-house tool to deploy scripts, which internally triggers the SQLCMD (shell) utility to execute T-SQL scripts in a db.
Here is the culprit: by default,
QUOTED_IDENTIFIERisOFFwhen running in SQLCMD mode!Every script run through this tool is created with
QUOTED IDENTIFIER OFF. We are the only module which uses indexed views. All the remaining stories you know well in my previous posts :(NOTE: I am going to vote everyone's post as useful.
编写存储过程的脚本，确保/更改 SET 选项，运行 ALTER PROC 以确保设置 SET QUOTED IDENTIFIER ON。
为什么？
“SET QUOTED IDENTIFIER”的设置是在创建存储过程时定义的，并且对于表始终为“ON”。 来源，BOL。
连接的默认值可以在服务器级别（sp_configure '用户选项'）或数据库级别（ALTER DATABASE）定义。 对于 SSMS，它位于“工具..选项..查询执行..SQL Server..ANSI”下。 它也是客户端库的默认设置（DB-LIb 除外）。
现在，如果您打开 SSMS 查询窗口并开始输入“CREATE PROC..”，那么它会在您运行代码时使用 SSMS 设置。
并且 SET QUOTED IDENTIFIER 不能在存储过程内的运行时设置。 在你不同意之前给我看看参考资料...来自上面的 MS BOL 链接：
您必须努力工作才能在关闭状态下运行任何代码...因此最可能的解决方法是更改或重新创建存储过程。
Script the stored proc, ensure/change SET options, run the ALTER PROC to ensure SET QUOTED IDENTIFIER ON is set.
Why?
The setting of "SET QUOTED IDENTIFIER" is defined at creation time for stored procs and is always "ON" for tables. Source, BOL.
The default for connections can be defined at the server level (sp_configure 'user options') or database level (ALTER DATABASE). For SSMS, it's under "Tools..Options.. Query Execution..SQL Server..ANSI". It's also the default for client libraries too (except DB-LIb).
Now, it you open an SSMS Query Window and start typing "CREATE PROC.." then it uses SSMS settings when you run the code.
And SET QUOTED IDENTIFIER can not be set at run time inside the stored proc. Show me the a reference before you disagree... From the MS BOL link above:
You have to work hard to run any code with this OFF... so the most likely fix is to ALTER or re-create the stored proc.
我刚刚读了 Erland Sommarskog 的这篇文章，动态 SQL 的诅咒与祝福，它包括以下关于 SET QUOTED IDENTIFIER 设置的段落：
您的存储过程是否使用 XQuery、索引视图或计算列上的索引？
I was just reading this article by Erland Sommarskog, The Curse and Blessings of Dynamic SQL, and it includes the following paragraph in regards to the SET QUOTED IDENTIFIER setting:
Does your stored procedure make use of XQuery, indexed views or indexes on computed columns at all?
在 SQL Server 2005 中，SET QUOTED IDENTIFIER 默认情况下处于关闭状态，而不是打开状态（除非使用 ODBC 或 OLE 连接...请参阅 此 了解更多信息）。
您无需使用 SET QUOTED IDENTIFIER ON 创建表即可使用它。
您需要做的就是将 SET QUOTED IDENTIFIER ON 添加到 SP 的开头，以使其能够运行该过程（并确保如果您不想将其保留为打开状态，则必须将 SET QUOTED IDENTIFIER OFF 切换为回来了）。
编辑
我纠正了。 根据此 MSDN 页面，设置引用标识符默认情况下为 ON（除非与 DB-Library 应用程序连接）。
In SQL Server 2005, SET QUOTED IDENTIFIER is OFF by default, not ON (unless using an ODBC or OLE connection...see this for more information).
You do not need to create the table with SET QUOTED IDENTIFIER ON to use it.
All you need to do is add SET QUOTED IDENTIFIER ON to the beginning of your SP to enable it for the run of the procedure (and make sure that if you don't wish to leave it on, you have SET QUOTED IDENTIFIER OFF to switch it back).
EDIT
I stand corrected. According to this MSDN Page, SET QUOTED IDENTIFIER is ON by default (unless connection with a DB-Library application.