Moving from 2000 2005 SP with Cursor Errors

Question

I am in the process of moving some of my company's internal reporting from 2000 to 2005.  Part of that includes moving some stored procedures and migrating DTS to SSIS. 

The problem I am having is with stored procedure - which I will refer to as SP1. 

SP1 calls second stored procedure (SP2)

SP2 returns a cursor

SP1 then opens a cursor and loops though the cursor returned by SP2.

When I run SP1 I get the following error:

Msg 16958, Level 16, State 3, Procedure ER_Load_Billrates, Line 21                                                                                   Could not complete cursor operation because the set options have changed since the cursor was declared.

I know cursors are not not great for design and performance, but the purpose of this project is migrate our internal reporting structure for billing to 2005, not redesign it (unless necessary).  So what i'm hoping to find out here is:

What does that error mean?

Why does it work in 2000 and not in 2005?

What can I do to correct that?

Below is the code  from the stored procedure:

DECLARE @r_ResultCrsr CURSOR, @r_Code int
DECLARE @r_Item NVARCHAR(255)
DECLARE @r_Description NVARCHAR(255)
DECLARE @r_Type NVARCHAR(255)
DECLARE @r_Price FLOAT
DECLARE @r_Resource NVARCHAR (255)
DECLARE @r_Project NVARCHAR(255)
DECLARE @r_SalesPerson NVARCHAR(255)
DECLARE @BillID int

-- Read the uploaded billing table data
EXECUTE dbo.ER_Read_BillRates
@ResultCrsr = @r_ResultCrsr OUTPUT
FETCH NEXT FROM @r_ResultCrsr
INTO  @r_Item,
 @r_Description,
 @r_Type, 
 @r_Price,
 @r_Resource,
 @r_Project,
 @r_SalesPerson
WHILE (@@FETCH_STATUS <> -1)
BEGIN

 --The Billing Table is Deleted When a load is run, so this should always be zero
 --But leave this check in place in the event that something is done outside the
 --Packaged load.

print @r_Resource
print @r_Project
 SET @BillID = 0
 SELECT @BillID = BillID
 FROM BillingRates
 WHERE Resource = @r_Resource
   AND Project = @r_Project   
 IF (@BillID = 0)
 BEGIN
  BEGIN TRANSACTION    
  --Store the Billing Record
  INSERT INTO BillingRates
    (Item,
    [Description],
    Type,
    Price,
    Resource,
    Project,
    [Sales Person]) 
  VALUES (@r_Item,
   @r_Description,
   @r_Type,
   @r_Price,
   @r_Resource,
   @r_Project,
   @r_SalesPerson)
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN @@ERROR
  END
  COMMIT TRANSACTION

 END
FETCH NEXT FROM @r_ResultCrsr
 INTO @r_Item,
 @r_Description,
 @r_Type, 
 @r_Price,
 @r_Resource,
 @r_Project,
 @r_SalesPerson
END
CLOSE @r_ResultCrsr
DEALLOCATE @r_ResultCrsr 

 

Thanks

 

 

 

Answer 1

You need to check the SET options in the SP dbo.ER_Read_BillRates against the SET options in effect in the TSQL batch. You can use DBCC USEROPTIONS to do this. Note that some SET options like QUOTED_IDENTIFIER get saved when you create the procedure. The error message indicates that some SET options changed between the SP call and the TSQL batch continuation.

Answer 2

I am in a similar situation..On the surface, I don't see any set options being changed after the trigger calls the stored procedure.  I need to return a scrollable cursor from my sproc (because I need to be able to perform multiple loops throught the sprocs result-set), and the cursor operation fails when I do a FETCH FIRST (to force the pointer to the top of the result-set)

Answer 3

I have the same issue when migrating SQL 2000 to SQL 2K5. I have a parent SP (sp_parent), in which there is a global cursor declared. In this sp_parent, there is a child sp (call it sp_child), in which the global cursor decalred in sp_parent is used, and then I got the exactly the same error message, i.e.

Msg 16958, Level 16, State 3, Procedure sp_child, Line 308

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

 

This happens only in SQL 2k5 (SP2+hotfix Developer Edition), but not in SQL 2000. So far, I cannot find a solution either.

PS: I have double-checked the Set option (by using DBCC useroptions) in both sp_parent and sp_child, I do not see there is any difference.

 

Is this a bug in SQL 2K5? 

 

thanks for any help / hint.

 

Jeff Yao

Answer 4

I had the same problem.

 

I had a large sql script that repeatedly declared a cursor,  then call a stored procedure that did a fetch next on the cursor declared.  SOME of the invocations of the stored procedure would fail with the 16958 error message,  and some would NOT!

 

I noticed that the ones that DIDN'T fail all had the ORDER clauses in the select statement that defined the cursor.  The select statements that didn't have ORDER clauses would  fail when the cursor was used in the SP.  I added ORDER clauses and the problem went away!!!!

 

HTH

 

 

Answer 5

I had the same problem.

 

I had a large sql script that repeatedly declared a cursor,  then call a stored procedure that did a fetch next on the cursor declared.  SOME of the invocations of the stored procedure would fail with the 16958 error message,  and some would NOT!

 

I noticed that the ones that DIDN'T fail all had the ORDER clauses in the select statement that defined the cursor.  The select statements that didn't have ORDER clauses would  fail when the cursor was used in the SP.  I added ORDER clauses and the problem went away!!!!

 

HTH