sql server 2005 - 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
...