Unanswered: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDEN
I am getting this message:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'
Only when updating a table that I recently put an Indexed View on. I have read all the white-papers I can find as to why this occurs, and they all say the same thing, that the QUOTED_IDENTIFIER needs to be enabled. I have done that as far as I can tell.
I altered the database setting QUOTED_IDENTIFIER ON, which should affect every connection.
Maybe this can serve as a FAQ for other people new to Indexed Views. I probably would have caught this sooner, but the error that I was seeing on the webpage didn't give me the full picture. There was a trigger that was erroring out as well.
Having said that, I was able to fix the problem by recompiling the stored procedure that was being called by the trigger.
Also if anyone ever has this trouble, just remember to try recompiling with the following options set.
SET NUMERIC_ROUNDABORT OFF
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
On another note. If you are attempting to use your newly created Indexed View and for some reason the optimizer just won't select the correct index, recompile the stored proc that does the select using the above options set once again.
Is it possible to tell what settings a stored procedure was compiled with? I am having similar problems related to them not having the right SET options. I am wanting to find a way to query the ones that need to be changed.
Well, I don't think there is a way to determine the actual set options for a specific stored procedure, but if you look at the dependencies, you should be able to determine what procs would be affected by an Indexed View and then just recompile them with the proper set options.