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

Question

i am having a problem with an update stored proc

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.

unfortuanltey, there is NO indexed views/computed columns/query notifications for this table

this sp was running fine for past couple of days and since today morning has been reporting 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 proc, the issue will be fixed for now but I want to understand what triggered this issue in the first place.

thanks

Answer 1

Some thoughts:

Did indexes get rebuilt? If you do index maintenance using DMO, then quoted_identifier will not always be preserved. It an be a paint to track down and was a particular problem is SQL Server 2000 until SP4 or so.

However, I've seen on SQL Server 2005 some time ago too.

Answer 2

i think what gbn saying may true in our case.. below the reasons

we cannot create a indexed view by setting the quoted identifier off... i just tried it.. 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, rebuilting 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 u would get an error while inserting, updating the table,,, but here i can get the error straight way,, so sql engine wont allow to create index on viwes by setting it to off.. here the link.. msdn link

i have asked the 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 ediror, this time also it throws the same error. so its clear we cant create indexes on views by turning off the quoted identifier

Cheers

Ramesh Vel