了解QUOTED_IDENTIFIER [英] Understanding QUOTED_IDENTIFIER
问题描述
我们只是遇到了一个问题，其中一个存储过程引发了错误；
We just ran into a problem with one of our stored procs throwing an error;
SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'
我通过修改存储的proc并将加引号的标识符设置为ON来修复它.事情是，我在CREATE PROCEDURE调用之前进行了此操作.例如；
I fixed it by modifiying the stored proc and setting the quoted identifier to ON. The thing is, I did this prior to the CREATE PROCEDURE call. For example;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertStuff]
我本以为这会影响CREATE PROCEDURE语句，但不会影响与执行该过程有关的任何事情.
I would have thought that this affected the CREATE PROCEDURE statement, but wouldn't have affected anything to do with the execution of that procedure.
我们的脚本全部作为drop和create脚本部署，并通过sqlcmd运行.我刚刚读到此处(搜索示例:执行SQLCMD)和此处，sqlcmd使用关闭引号标识符.我已经更改了脚本，以包括-I开关以查看是否可以解决我们的问题.
Our scripts are all deployed as drop and create scripts and run via sqlcmd. I've just read that here (search for Example: Executing SQLCMD) and here that sqlcmd executes with quoted identifier off. I've changed our script to include the -I switch to see if that fixes our issues.
那是我的问题；
1)SET QUOTED_IDENTIFIER ON语句仅影响DDL CREATE PROCEDURE语句，还是也影响存储过程的执行?我的快速测试表明是后者.
1) Does the SET QUOTED_IDENTIFIER ON statement affect only the DDL CREATE PROCEDURE statement, or does it also affect the execution of the stored proc also? My quick test indicates the latter.
2)由于此开关的默认设置为ON，因此我假设通过设置sqlcmd查询的-I开关不会产生不利影响.出于所有目的和目的，我将假定它与复制脚本的内容，然后将其粘贴到查询管理器中并单击执行相同.如果我对此有误，请纠正我.我们的简单部署脚本如下；
2) As the default for this switch is ON, I am presuming that by me setting the -I switch of my sqlcmd query will have no adverse affects. For all intents and purposes, I will assume it is the same as copying the contents of the script and then pasting them into query manager and hitting execute. Please correct me if I am wrong about this. Our simple deploy script is as follows;
@echo off
SET dbodir=../Schema Objects/Schemas/dbo/Programmability/Stored Procedures/
SET tpmdir=../Schema Objects/Schemas/TPM/Programmability/Stored Procedures/
echo --- Starting dbo schema
for %%f in ("%dbodir%*.sql") do (echo Running %%f.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%dbodir%%%f")
echo --- Completed dbo schema
echo --- Starting TPM schema
for %%g in ("%tpmdir%*.sql") do (echo Running %%g.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%tpmdir%%%g")
echo --- Completed TPM schema
pause
预先感谢
似乎有一些更多信息来确定存储用于存储过程的SET选项，对此的公认答案提供了有关适用于SET选项的通用优先顺序的一般规则的一些详细信息.对此的评论还指出；
It seems as though there is some further info to determine where the SET options for stored procs are stored, and the accepted answer to this provides some details on general rules regarding generic order of precedence that applies to the SET options. The comments on this also state that;
"...在过程创建时仅捕获QUOTED_IDENTIFER和ANSI_NULLS设置."
"...在存储过程中无法在运行时设置
" ...Only QUOTED_IDENTIFER and ANSI_NULLS settings are captured at procedure creation time." "...SET QUOTED IDENTIFIER can not be set at run time inside the stored proc" (my emphasis).
我觉得这回答了我的第一个问题.
I feel that answers my first question.
第二部分有人吗?
推荐答案
我将以下命令保存到文本文件，然后使用SQLCMD执行该命令:
I saved the following command to a textfile, then executed it with SQLCMD:
SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER OFF
在SQL Profiler中签入，SQLCMD -i <filename>使用我系统上的以下连接选项进行连接:
Checking in SQL profiler, SQLCMD -i <filename> connects with the following connection options on my system:
-- network protocol: LPC
set quoted_identifier on
...
但是SQLCMD在连接时发出以下命令:
however the following command issued by SQLCMD when it connects:
SET QUOTED_IDENTIFIER OFF SET TEXTSIZE 4096
然后它运行我的脚本.
因此，对2)的回答是否定的-使用SQLCMD -i运行脚本与从SSMS执行脚本(具有默认连接选项)不同.如果脚本需要QUOTED_IDENTIFIER ON，则如果要以这种方式执行它，则需要在开始时显式设置它.
So, the answer to 2) is no - running a script with SQLCMD -i is not the same as executing from SSMS (with the default connections options). If a script requires QUOTED_IDENTIFIER ON, then you need to explicitly set it at the start if you're going to execute it this way.
这篇关于了解QUOTED_IDENTIFIER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
