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 Tuesday, January 4, 2005 4:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 11, 2011 12:35 AM
Points: 3, 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

Post #153433
Posted Wednesday, January 5, 2005 1:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:32 AM
Points: 1,327, Visits: 4,504

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.




Post #153658
Posted Wednesday, January 5, 2005 1:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 11, 2011 12:35 AM
Points: 3, Visits: 9

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

Michael A. Martin

Post #153659
Posted Wednesday, January 5, 2005 2:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 2:15 AM
Points: 278, Visits: 303

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




Post #153676
Posted Wednesday, January 5, 2005 4:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 11, 2011 12:35 AM
Points: 3, 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

Post #153702
Posted Wednesday, January 5, 2005 4:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 2:15 AM
Points: 278, Visits: 303

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)




Post #153704
Posted Wednesday, April 11, 2007 10:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 15, 2007 5:40 PM
Points: 2, 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.

 

 

 

 

Post #357713
Posted Thursday, April 12, 2007 2:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 2:15 AM
Points: 278, Visits: 303
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.


Post #357733
Posted Thursday, April 12, 2007 6:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 15, 2007 5:40 PM
Points: 2, 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

Post #358069
Posted Friday, April 13, 2007 10:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 2:15 AM
Points: 278, Visits: 303

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.




Post #358328
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse