Menu
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
512
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
Note: If we change the QUOTED_IDENTIFIER option SSMS tools, it turns off the QUOTED_IDENTIFIER for all client session. You should be careful about modifying this option in the production environment. We can use the SQL execution plan to view the current value of QUOTED_IDENTIFIER.
For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values. ANSI_NULLS should be set to ON for executing distributed queries.
When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created. When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.
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_IDENTIFIERandSET ANSI_NULLSwhen 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 forSET QUOTED_IDENTIFIERandSET ANSI_NULLSare ignored when the stored 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.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
