Has anybody seen the following error message:
"Could not complete cursor operation because the set options have changed since the cursor was declared."
This error is being generated when a stored procedure is executed. The sp creates a cursor based upon a select statement. The cursor is then processed to delete rows from 2 tables. This sp only fails if the primary table used by the cursor contains 0 rows. So, if there are rows in the primary table, even if none meet the delete criteria, the sp completes. Otherwise, error 16958. I have not been able to find anything, anywhere on this error. Hope someone can help.
Michael A. Martin
I had a similar problem problem some time ago, with a simple system stored procedure I had written (i.e. it was in the master database, and the name began sp_). The same procedure in a user database or without the sp_ name ran OK.
The rather bizarre solution was to put 'SET ARITHABORT OFF', followed by 'SET ARITHABORT ON' at the beginning of the procedure, and it ran OK.
Just tried your suggested solution, and unfortunately, it did not work. Thanks anyway. Anybody else have a suggestion?
Yup....post the code so that we can examine it.....and get rid of the cursor.
If you don't know why I'm advocating the latter route....search here for 'good performance' and 'cursor'.....the 2 phrases rarely go together!
99.99% of SQL actions can be done without cursors....with a (noticable)performance boost 99.99% of the time.....
Sorry, can't get rid of the cursor. I am not the "owner" of the stored procedure. It is maintained by another organization. They would be responsible for making any proposed change. That is also why I may not post the code.
maybe point them here then!!!!
Good luck with your travails....getting rid of the cursor should solve more than 1 problem....1 definite one you have...and 1 you may not be aware of...(performance)
I too have just discovered the same error when running the same SP that I run in 2000 on our new test 2005 database. This is a programatic sp which runs in seconds on 2000 so performance is not an issue...
Msg 16958, Level 16, State 3, Server ADELGO12V02, Procedure Time_20, Line 45Could not complete cursor operation because the set options have changed since the cursor was declared.Msg 16917, Level 16, State 2, Server ADELGO12V02, Procedure Time_20, Line 47Cursor is not open.Msg 16917, Level 16, State 1, Server ADELGO12V02, Procedure Time_20, Line 143Cursor is not open.
declare calendar_cursor insensitive cursor for
select calendar, descrfrom otherDatabase.otherUser.cclcald with (nolock)
fetch nextfrom calendar_cursorinto @current_calendar, @curr_cal_desc
I have just started investigating this so if I find out anything I will let you know.
The purpose of the sp is to create a calendar table that contains info about dates that users can sql in conjuction with other user tables with time / date data already converted in various formats. Note that the cursor table is not updated.
This is the full sp...
create procedure [localTableOwner].[Time_20] @job int = 0as
set nocount on
declare @current_calendar char(50) declare @curr_cal_desc char(50) declare @count intdeclare @dateend datetimedeclare @dateseed datetimedeclare @error_var intdeclare @error_message char(50) declare @is_work_day char(1)declare @number_of_years char(3)declare @parm_value char(80)declare @previous_calendar char(10) declare @row_count int declare @start_date char(23)
truncate table [localTableOwner].[Time_Calendar]
set identity_insert [localTableOwner].[Time_Calendar] on
insert into [localTableOwner].[Time_Calendar] (Time_key) values (1)
set identity_insert [localTableOwner].[Time_Calendar] off
exec [localTableOwner].[get_config_info] 'Time','Start Date', @parm_value outputset @start_date = CONVERT (datetime, @parm_value ,103)
exec [localTableOwner].[get_config_info] 'Time','Number of Years', @parm_value outputset @number_of_years = substring(@parm_value,1,3)
set datefirst 1 -- 1 = Mon (7 = Sun).
while @@FETCH_STATUS = 0begin
-- Reset the @dateseed and @dateend variables prior to processing all the dates for the current -- calendar set @dateseed = cast(@start_date as datetime) set @dateend = dateadd(yy,cast(@number_of_years as int),@dateseed)
while @dateseed < @dateend and @current_calendar is not null begin insert into [localTableOwner].[Time_Calendar] -- Insert Fields. ( [Date], [Calendar_Year], [Calendar_Month], [Calendar_Day], [Day], [Dy], [Day_of_Week], [Month], [Mth], [Quarter], [Quarter_Name], [Qtr], [IS_Weekend], [Calendar], [Calendar_Description] )
values -- With Values. ( @dateseed, datepart(yyyy,@dateseed), datepart(mm,@dateseed), datepart(dd,@dateseed), datename(dw,@dateseed), case datename(dw,@dateseed) -- Day Name. when 'Monday' then 'Mon' when 'Tuesday' then 'Tue' when 'Wednesday' then 'Wed' when 'Thursday' then 'Thu' when 'Friday' then 'Fri' when 'Saturday' then 'Sat' else 'Sun' end, datepart(dw,@dateseed), datename(mm,@dateseed), -- Month Name. case datepart(mm,@dateseed) -- Short Month Name. when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug' when 9 then 'Sep' when 10 then 'Oct' when 11 then 'Nov' else 'Dec' end, datepart(qq,@dateseed), -- Quarter Number. case datename(qq,@dateseed) -- Quarter Name. when 1 then '1st Quarter' when 2 then '2nd Quarter' when 3 then '3rd Quarter' else '4th Quarter' end, case datename(qq,@dateseed) -- Short Quarter Name. when 1 then '1Qtr' when 2 then '2Qtr' when 3 then '3Qtr' else '4Qtr' end, case datename(dw,@dateseed) -- Weekend Indicator. when 'Saturday' then 'T' when 'Sunday' then 'T' else 'F' end, @current_calendar, @curr_cal_desc ) set @dateseed = @dateseed + 1 end
fetch next from calendar_cursor into @current_calendar, @curr_cal_descend
can't see anything "obvious" wrong in the logic...i can see a need for the cursor...as the output/effect of 1 row effects the next row being processed but can't obviously see a set-based workaround (unless you could join to a numbers table and limit the number of records in select based on some equality/inequality/relationship to some target date from another table)
the basic insert/select can be done outside of a cursor, the cursor at the moment just seems to control how many times an insert takes place, varying 1 (key) data value within the loop.
it's hard to work though theoretical problems without access to sample raw data and matching expected output...(a comment not a criticism)
can you trace how far it's getting through the process, but putting in print statements etc??
I'm working in ireland, so it's friday and beer-time now...so next reply/feedback would be Monday.