Solved

Can't figure out UPDATE error in Stored Procedure

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard 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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

628 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