Could not complete cursor operation 16958
-
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.
Thanks,
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?
Michael A. Martin
-
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.
Michael A. Martin
-
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 45
Could 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 47
Cursor is not open.
Msg 16917, Level 16, State 1, Server ADELGO12V02, Procedure Time_20, Line 143
Cursor is not open.
SP Time_20...
declare calendar_cursor insensitive cursor for
select calendar, descr
from otherDatabase.otherUser.cclcald with (nolock)
open calendar_cursor
fetch next
from calendar_cursor
into @current_calendar,
@curr_cal_desc
I have just started investigating this so if I find out anything I will let you know.
-
Can you post the full SP?...and maybe a brief description of what its trying to do. You can hide/obfuscate any identifying remarks/code if there are confidentiality issues at play.
-
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 = 0
as
set nocount on
declare @current_calendar char(50)
declare @curr_cal_desc char(50)
declare @count int
declare @dateend datetime
declare @dateseed datetime
declare @error_var int
declare @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
declare calendar_cursor insensitive cursor for
select calendar, descr
from otherDatabase.otherUser.cclcald with (nolock)
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 output
set @start_date = CONVERT (datetime, @parm_value ,103)
exec [localTableOwner].[get_config_info] 'Time','Number of Years', @parm_value output
set @number_of_years = substring(@parm_value,1,3)
set datefirst 1 -- 1 = Mon (7 = Sun).
open calendar_cursor
fetch next
from calendar_cursor
into @current_calendar,
@curr_cal_desc
while @@FETCH_STATUS = 0
begin
-- 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_desc
end
close calendar_cursor
deallocate calendar_cursor
go
-
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.
-
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
-
Try explicitly declaring your cursor as Local
-
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.
-
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.
-
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! 🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply