INSERT Failed because the following SET options have incorrect settings: 'ANSI_NULLS,QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
在存储过程中编写脚本时,自定义安装程序不使用正确的设置.
在sp中设置这些(SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON;)无效：
在调用代码中的sp之前,我还尝试为open连接设置这些选项.这再次没有达到预期的效果.
看来,当sp运行到数据库时,与数据库连接的设置是重要的,而不是使用sp时的设置.
我已经通过在SSMS选项中使用这些设置进行了实验,这似乎就是这种情况.我希望有人确认这是绝对的情况(如果有一种方法我会喜欢听到它,但我没有希望)
不幸的是,改变安装程序对我来说当前不是一个选择,所以我期待不得不回滚几周的工作;所以,如果我必须这样做,我想确定(并有一些证据支持我),这是唯一的选择
默认情况下,SSMS具有正确的设置(sqlcmd,osql等也是如此).
从BOL,CREATE PROC,“Using SET Options”起
The Database Engine saves the settings
of bothSET QUOTED_IDENTIFIERandSETwhen a Transact-sql stored
ANSI_NULLS
procedure is created or modified.
These original settings are used when
the stored procedure is executed.
Therefore,any client session settings
forSET QUOTED_IDENTIFIERandSETare ignored when the stored
ANSI_NULLS
procedure is running. Other SET
options,such as SET ARITHABORT,SET
ANSI_WARNINGS,or SETANSI_PADDINGSare not saved when a stored procedure is created or modified.
