SELECT 失败，因为下列 SET 选项的设置不正确: 'QUOTED_IDENTIFIER'。问题处理

错误提示：

SELECT 失败，因为下列 SET 选项的设置不正确: 'QUOTED_IDENTIFIER'。请确保 SET 选项正确无误，可以用于 计算列

上的索引视图和/或索引和/或筛选的索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作。。

错误再现：

--SET QUOTED_IDENTIFIER ON 
--GO

CREATE PROCEDURE usp_test2
AS
BEGIN
	PRINT 2  
	
    --Executing XQuery and XML data modification statements 
    --requires that the connection option QUOTED_IDENTIFIER be ON.
    --执行xquery和xml数据修改语句，要求连接的QUOTED_IDENTIFIER选项设置为ON
	SELECT (
	           SELECT ',' + NAME
	           FROM   (
	                      SELECT 'a' AS 'name' UNION SELECT 'b' UNION SELECT 'c'
	                  ) AS a
	                  FOR XML PATH(''),
	                  TYPE
	       ).value('.', 'nvarchar(max)')
END

CREATE PROCEDURE usp_test1  
AS  
BEGIN  
 PRINT 1  
 EXEC usp_test2  
END

执行下面的语句再现错误

SET QUOTED_IDENTIFIER ON
EXEC usp_test1

会出现下面的错误：

1
2
消息 1934，级别 16，状态 1，过程 usp_test2，第 6 行
SELECT 失败，因为下列 SET 选项的设置不正确: 'QUOTED_IDENTIFIER'。请确保 SET 选项正确无误，可以用于 计算列上的索引视图和/或索引和/或筛选的索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作。。
 

错误原因分析：

因为在执行xml的xquery操作的时候，要求选项QUOTED_IDENTIFIER设置为ON才可以，否则就会报上面的错误；

这里需要注意的是，QUOTED_IDENTIFIER，ANSI_NULLS选项在创建过程或者函数的时候，会附在过程或者函数的对象上，

成为过程或者函数的属性，每次执行的时候会以这样的选项执行过程或者函数。

所以尽管在外部对相关的选项进行了设置，将QUOTED_IDENTIFIER设置成了ON，但是真正执行时也是使用过程或者函数自身存储的属性执行；

要想改变这个设置，只有在定义函数或者过程的时候，在定义的时候在定义函数开始设置好这些选项。

这里查询一个过程或者函数的选项的脚本如下：

-- Get the function name, definition, and relevant properties  
	SELECT sm.object_id,   
	   OBJECT_NAME(sm.object_id) AS object_name,   
	   o.type,   
	   o.type_desc,   
	   sm.definition,  
	   sm.uses_ansi_nulls,  
	   sm.uses_quoted_identifier,  
	   sm.is_schema_bound,  
	   sm.execute_as_principal_id  
	-- using the two system tables sys.sql_modules and sys.objects  
	FROM sys.sql_modules AS sm  
	JOIN sys.objects AS o ON sm.object_id = o.object_id  
	-- from the function 'dbo.ufnGetProductDealerPrice'  
	WHERE sm.object_id = OBJECT_ID('dbo.usp_test2')  
	ORDER BY o.type;  
	GO 

执行结果：

可以看到usp_test2的uses_quoted_identifier属性为零，即没有开启，所以会报这个错误；

错误处理

只需要将usp_test2的定义改成下面这样就可以了：

--在这里对过程的quoted_identifier属性进行设置
--这里的设置会跟随过程的每次执行
SET QUOTED_IDENTIFIER ON 
GO

CREATE PROCEDURE usp_test2
AS
BEGIN
	PRINT 2  
	
    --Executing XQuery and XML data modification statements 
    --requires that the connection option QUOTED_IDENTIFIER be ON.
    --执行xquery和xml数据修改语句，要求连接的QUOTED_IDENTIFIER选项设置为ON
	SELECT (
	           SELECT ',' + NAME
	           FROM   (
	                      SELECT 'a' AS 'name' UNION SELECT 'b' UNION SELECT 'c'
	                  ) AS a
	                  FOR XML PATH(''),
	                  TYPE
	       ).value('.', 'nvarchar(max)')
END

再次重复执行上面的语句，即便不用set quoted_identifier on 语句也一样可以成功执行，具体如下：

EXEC usp_test1

执行结果如下：

所以综上，在使用xml相关方法时，一定要记得开启set quoted_identifier on选项，如果是过程或者是函数中使用到了，记得在过程和函数的定义处，一定要开启这个选项；

 

再有，同样的过程或者函数在不同的数据库上可能也会表现出不一样的行为，即一个报错，另一个可能不报；

问题的原因在于，在没有明确设置这个选项的时候，这个选项会用系统预定义的选项，所以，如果在数据库上已经开启的情况下定义了这样的方法或者函数，那么就会不报错，而如果数据库上没有开启，使用上面语法的过程或者函数就会报这个错误了。