Can't figure out UPDATE error in Stored Procedure
Posted on 2011-03-02
I have had a stored procedure running successfully for about 3 months -- 4 times a day. Today the stored procedure errored out with the following error:
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 filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
I don't have any indexes to worry about.
I am doing an UPDATEs, but like I said, I have probably ran this procedure through a SQL Agent job 400 times without any problem. What is interesting is that I can open a Query Window and run the (UPDATE) statements copied right out of the procedure but it will not run in a SQL Agent job and I cannot run it if I EXECUTE the stored procedure in a Query Window.
I'm not sure the contents of the procedure is important but the beginning looks like this (as do all my sprocs):
/****** Object: StoredProcedure [dbo].[DailyCensus_Detail] Script Date: 03/02/2011 17:41:08 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
ALTER PROCEDURE [dbo].[MyProcName] AS
...Then some UPDATE statements like this:
SET ColumnName = 'Y' WHERE Acct >= '11000000007' AND Acct <= '11499998984'
OR Acct >= '31000000005' AND Acct <= '31499999998'
Please note all my Stored Procs have the same heading as this one.