Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Could not complete cursor operation 16958 Expand / Collapse
Author
Message
Posted Friday, April 13, 2007 10:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:22 AM
Points: 1,327, Visits: 4,506

Have a look at my earlier post about putting the set statements at the beginning of the procedure.

The bizarre solution was courtesy of Microsoft Support




Post #358330
Posted Friday, April 13, 2007 11:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 25, 2013 10:54 AM
Points: 186, Visits: 1,022
Try explicitly declaring your cursor as Local 
Post #358340
Posted Friday, May 18, 2007 11:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 5:23 AM
Points: 197, Visits: 152

Had the same error on a SQL 2005 server that had a stored proc that used a cursor that enumerated the user databases then ran a SQL command against each database.

On investigation I found it was failing on a database that had IsArithmeticAbortEnabled (this was the only difference between all of teh user databases). This was conflicting with the default behaviour of the stored procedure which was SET ARITHABORT OFF.

You may need to make sure that all of your database options align with the options for the stored procedure.




Post #367390
Posted Tuesday, May 29, 2007 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 21, 2014 8:05 AM
Points: 3, Visits: 99
I had a similar problem and resolved it by moving the 'set datefirst' statement to the beginning of the script.  Not sure why this worked.
Post #369625
Posted Friday, May 22, 2009 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:08 AM
Points: 7, Visits: 255
Hey,
Just came across the same error and realised a had a 'SET DATEFIRST' after the cursor was decalred. Moved this to the top of the SP and and now it works no problems.
Had a look on the microsoft site and they suggested upgrading to SP2 for SQL 2005 so might be worth trying that too.
Hope that helps!
Post #722133
Posted Thursday, June 20, 2013 9:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
Whilst I did not have any set datefirst statements in my SP, adding it got the SP to work.

Funny thing, the SP had been working for six weeks on 25+ different servers (2005 to 2012); then, all of a sudden, this weekend it fails with the error:

Could not complete cursor operation because the set options have changed since the cursor was declared.

It's even stanger since I only use the reserved word set to set variables, so adding a set datefirst 7 makes no sense whatsoever, but it works


/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Post #1465785
Posted Thursday, October 17, 2013 1:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 8:23 AM
Points: 3, Visits: 8
declare calendar_cursor insensitive cursor for
select calendar, descr
from otherDatabase.otherUser.cclcald with (nolock)


Looks like your using a dynamic cursor - getting data from another database. Check the compatibility levels of the databases (Db Properties / Options). They should be the same.
Post #1505890
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse