Question

I have a stored procedure which takes an XML parameter and inserts the data into multiple tables. If I run the stored procedure into a database using a SSMS query window, everything works fine. However, we have a custom installation program that is used to deploy stored procedures to databases, and when this is used, execution of the sp fails with this error:

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.

The custom installation program does not use the correct settings when scripting in the stored procedures.

Setting these ( SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON;) within the sp has no effect:

I have also tried setting these options for the open connection just before calling the sp in the code. This again does not have the desired effect.

It appears that the settings on the connection to the database while the sp is being run in to the database are what matters, not the settings when the sp is used.

I have experimented by playing with these settings in SSMS options, and this does appear to be the case. I would just like someone to confirm that this is definitely the case (if there is a way around I would love to hear it, but I m not hopeful)

Unfortunately altering the installer program is not an option for me at the present time, so I m looking at having to roll back a couple of weeks work; so if I do have to do this I want to be really sure (and have some evidence to back me up) that this is the only option

Answer 1

The settings applied with those at CREATE or ALTER time and are ignored at runtime.

SSMS has correct settings by default (so does sqlcmd, osql etc).

From BOL, CREATE PROC, "Using SET Options"

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL stored procedure is created or modified. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the stored procedure is running. Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or modified.

友情链接