SQL Server:INSERT/UPDATE/DELETE失败，因为以下SET选项的设置不正确:"QUOTED_IDENTIFIER" [英] SQL Server: INSERT/UPDATE/DELETE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’
问题描述
我有一个相当尴尬的问题:
I have this rather awkward problem:
两个星期以来，每当我使用SQL脚本更新/创建存储过程后，这些存储过程在运行时都会因上述错误而失败.
For two weeks now, whenever after I've updated/created stored procedures using my SQL scripts, when these stored procedures are run, they fail with above error.
其他有关此问题的帖子对我的情况没有帮助.
Other posts dealing with this problem didn't help in my case.
这里有许多参数，有助于排除不适用于我的情况的常见解决方案:
Here's a number of parameters, helping to exclude common solutions which do not apply in my case:
-
我的存储过程脚本在笔记本电脑(SQL Server 2012，Windows Server 2008 R2)上可以正常工作.
My stored procedure scripts work flawlessly on my laptop (SQL Server 2012, Windows Server 2008 R2).
我的存储过程脚本可以在任何其他计算机(安装了SQL Server 2012的构建计算机，安装了SQL Server 2005的TEST服务器和安装SQL Server 2005的PROD服务器)上正确创建存储过程已安装).但是，存储过程将不会在我的任何其他机器上运行.
My stored procedure scripts correctly create stored procedures on any other machine (which is our build machine, with SQL Server 2012 installed; our TEST server, with SQL Server 2005 installed, and our PROD server, with SQL Server 2005 installed). However, the stored procedures won't run on any other machine than mine.
我正在我的机器(就像这里的任何其他机器一样)上使用生产SQL Server(SQL Server 2005)的数据库备份.
I'm using a database backup of our production SQL Server (SQL Server 2005) on my machine (like any other machine here does).
即使是最基本的存储过程也会失败(例如DELETE myTable WHERE ID = @delID).
Even the most basic stored procedure fails (e. g. DELETE myTable WHERE ID = @delID).
在我检查过的每个SQL Server安装中，在服务器级别和数据库级别上，带引号的标识符都设置为OFF(！).那么，为什么我的存储过程突然需要将此选项设置为ON?
On every SQL Server installation I've checked, quoted identifier is set to OFF (!), both on server and on database level. So why do my stored procedures all of a sudden require to have this option set to ON?
我正在使用SQLCMD运行脚本.这给了我一个选择，可以在USE语句中动态设置服务器实例的数据库名称.
I'm using SQLCMD to run my scripts. This gives me an option to dynamically set the server instance's database name in the USE statement.
我的脚本仅在ALTER PROCEDURE之后紧跟着USE语句；或者IF EXISTS (...) DROP PROCEDURE ... GO; CREATE PROCEDURE ...
My scripts only contain a USE statement and right after the ALTER PROCEDURE; or alternatively IF EXISTS (...) DROP PROCEDURE ... GO; CREATE PROCEDURE ...
这一切已经工作了好几年了，但是突然之间，自两个星期前以来，用我的脚本创建的存储过程突然失败了.
This all worked for years now, but suddenly, since two weeks ago, stored procedures created with my scripts suddenly fail.
我知道我可以在脚本中手动将QUOTED_IDENTIFIER设置为ON-但我不想这样做.这里有问题.我想知道那是什么问题.
I know that I could manually set QUOTED_IDENTIFIER to ON in my scripts - but I don't want to. There is something wrong here. I want to know what that problem is.
这是怎么回事?
推荐答案
SQLCMD默认将QUOTED_IDENTIFIER选项设置为OFF.您可以使用-I选项进行更改.
SQLCMD sets the QUOTED_IDENTIFIER option to OFF by default. You can change it with -I option.
这篇关于SQL Server:INSERT/UPDATE/DELETE失败，因为以下SET选项的设置不正确:"QUOTED_IDENTIFIER"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
