Solved

Can't figure out UPDATE error in Stored Procedure

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

14 Experts available now in Live!

Get 1:1 Help Now