Solved

Can't figure out UPDATE error in Stored Procedure

Posted on 2011-03-02
5
194 Views
Last Modified: 2012-08-13
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):

USE [mydb]
GO
/****** Object:  StoredProcedure [dbo].[DailyCensus_Detail]    Script Date: 03/02/2011 17:41:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[MyProcName] AS

...Then some UPDATE statements like this:

UPDATE mytable
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.

Any ideas?

Thanks!


0
Comment
Question by:CalBob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35023860
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35023906
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35023920
0
 

Accepted Solution

by:
CalBob earned 0 total points
ID: 35032220
I am going going to close this question because I figured out what was wrong.  I'm not sure what the proper solution is but at least I know the cause and have a solution for myself.

As I dug into the problem I realized it was just one particular table that was being updated that was the problem.  Furthermore, I remembered I had added a derived column on that table the day I started having problems.  Since I couldn't get the UPDATE to work when I ran the stored procedure through SQL Agent I decided to delete the derived column and populate the column in the stored procedure.  Since this is a very small table it worked fine.  

Thanks for your suggestions.
0
 

Author Closing Comment

by:CalBob
ID: 35067798
In my situation, my solution was the best solution.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question