UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Question

I am having a problem with an update stored procedure. The error is:

UPDATE failed because the following SET options have incorrect settings: '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.

SQL State: 42000
Native Error: 1934

Unfortunately, there are no indexed views, computed columns, or query notifications for this table. This Stored Procedure was running fine for past couple of days and since today has been returning this error.

Is there any suggestion that would help in identifying the problem?

Note: If I set the quoted_identifier to ON and rerun the CREATE PROCEDURE, the issue will be fixed (for now). But I want to understand what triggered this issue in the first place.

Answer 1

To avoid that error, I needed to add

SET ANSI_NULLS, QUOTED_IDENTIFIER ON; 

for all my stored procs editing a table with a computed column.

You don't need to add the SET inside the proc, just use it during creation, like this:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON; GO  CREATE PROCEDURE dbo.proc_myproc ... 

Answer 2

We cannot create a indexed view by setting the quoted identifier off. I just tried it and SQL 2005 throws an error straight away if it is turned off:

Cannot create index. Object 'SmartListVW' was created with the following SET options off: 'QUOTED_IDENTIFIER'.

As gbn said, rebuilding the indexes must be the only other way it got turned off.

I have seen lots of articles saying it must be on before creating index on views. Otherwise you would get an error while inserting, updating the table, but here I can get the error straight away, so sql engine won't allow to create index on views by setting it to off, per this msdn link.

I have asked a similar question here in stack sometime ago...

EDIT

I turned off the global queryexecution (in editor) ANSI settings and ran the index script in new editor, this time also it throws the same error. So it's clear we can't create indexes on views by turning off quoted_identifier.