SELECT 失败: 请确保 SET 选项可正确用于计算列和/或查询通知和/或 xml 数据类型方法的索引视图和/或索引。

longshujun 2010-12-10 10:51:43

SELECT 失败，因为下列 SET 选项的设置不正确:'QUOTED_IDENTIFIER'。请确保 SET 选项可正确用于计算列和/或查询通知和/或 xml 数据类型方法的索引视图和/或索引。

执行以下的存储过程（EXEC [dbo].[SelectMbxxBljlk_Print] 527,2,86945 ）就报错，其实已经设置了 SET QUOTED_IDENTIFIER ON ，
但是如果单独执行里面的语句，不用存储过程就不报错，真的很奇怪的原因
alter procedure [dbo].[SelectMbxxBljlk_Print]
(
@idm int,
@mblb int,@syxh int
)

as
-- EXEC [dbo].[SelectMbxxBljlk_Print] 527,2,86945

set nocount on
Set ARITHABORT ON
SET QUOTED_IDENTIFIER ON

if @mblb=2
begin

SET QUOTED_IDENTIFIER ON

SELECT idm,name,mbnr,
mbnr.query('<Model> { for $i in /Model/* order by $i/@xssx return $i } </Model>') as mbnrNode,0 as have from bl_mbk where idm=@idm
and NOT EXISTS (select bldm from bl_bljlk_print where bldm=@idm and syxh=@syxh )
union all
SELECT bldm as idm,blmc as name,blnr as mbnr,
blnr.query('<Model> { for $i in /Model/* order by $i/@xssx return $i } </Model>') as mbnrNode,1 as have from bl_bljlk_print where bldm=@idm and syxh=@syxh
--and EXISTS (select bldm from bl_bljlk where bldm=@idm and syxh=@syxh )
--@@rowcount=0

end

单独执行就不报错

SET QUOTED_IDENTIFIER ON

SELECT idm,name,mbnr,
mbnr.query('<Model> { for $i in /Model/* order by $i/@xssx return $i } </Model>') as mbnrNode,0 as have from bl_mbk where idm=@idm
and NOT EXISTS (select bldm from bl_bljlk_print where bldm=@idm and syxh=@syxh )
union all
SELECT bldm as idm,blmc as name,blnr as mbnr,
blnr.query('<Model> { for $i in /Model/* order by $i/@xssx return $i } </Model>') as mbnrNode,1 as have from bl_bljlk_print where bldm=@idm and syxh=@syxh
--and EXISTS (select bldm from bl_bljlk where bldm=@idm and syxh=@syxh )

...全文