SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Could not complete cursor operation 16958


Could not complete cursor operation 16958

Author
Message
Michael A. Martin
Michael A. Martin
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 9

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


Ian Scarlett
Ian Scarlett
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4990 Visits: 7136

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.





Michael A. Martin
Michael A. Martin
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 9

Just tried your suggested solution, and unfortunately, it did not work. Thanks anyway. Anybody else have a suggestion?

Michael A. Martin


AndrewMurphy
AndrewMurphy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 304

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.....





Michael A. Martin
Michael A. Martin
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 9

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


AndrewMurphy
AndrewMurphy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 304

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)





Jim Bird-428654
Jim Bird-428654
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 1

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.


AndrewMurphy
AndrewMurphy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 304
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.



Jim Bird-428654
Jim Bird-428654
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 1

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


AndrewMurphy
AndrewMurphy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 304

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.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search