Solved

Can't figure out UPDATE error in Stored Procedure

Posted on 2011-03-02
5
190 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now