Solved

Can't figure out UPDATE error in Stored Procedure

Posted on 2011-03-02
5
186 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
Comment Utility
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
0
 

Accepted Solution

by:
CalBob earned 0 total points
Comment Utility
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
Comment Utility
In my situation, my solution was the best solution.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

8 Experts available now in Live!

Get 1:1 Help Now