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