Interesting error!!
-
I have a trigger which declares a global cursor, a sp opens this global cursor and does some dml based on values read. Scenario works perfectly on Microsoft SQL Server 2000 - 8.00.194 but does not works in sql server 2005 - 9.00.1399.
Msg 16958, Level 16, State 3, Procedure usp_update_employee, Line 5
Could not complete cursor operation because the set options have changed since the cursor was declared.
I guess, installing SP2 (build 2153) could solve my problem, but not sure about the same. I don't have SP2 with me; also not sure whether installing this patch would solve error. Does anybody encounter this error before?
Regards
Shrikant Kulkarni -
I thought that SP2 would make this scenario workable but I am getting same exception after upgrading SQL Server 2005 to build 2153. Have a look at following script, please note that script does not have any business logic. it is written to find out reason of it being working on SQL server 2000 and not on SQL Server 2005.
use tempdb
create table employee (ID int, ename varchar(10), deptid int)
create table dept (Id int, deptname varchar(10))
go
create trigger dept_updt_trig
on dept
for update
as
begin
declare c1 cursor global for
select id from inserted
exec usp_update_employee
end
go
create procedure usp_update_employee
as
begin
declare @empl_dept_id int
open global c1
fetch c1 into @empl_dept_id
update employee set deptid=@empl_dept_id
close c1
deallocate c1
end
go
insert into employee select 1, 'shri',1
insert into dept select 1,'Account'
update dept set id=2
select * from employee
select * from dept
go
drop procedure usp_update_employee
drop trigger dept_updt_trig
drop table employee
drop table dept
Regards
Shrikant Kulkarni -
I went to the doctor once and said "Doc, if I hold my arm up like this, it hurts like hell." The doctor said "So... don't hold you arm up like that."
My point is, I see nothing in the code that you posted that requires a cursor, much less, a global cursor... Rewrite the code to set-based and, not only will your problem go away, you will enjoy the benefit of much improved performance on the updates, as well.
And, yes, I understand that your code is just "proof of principle" code... but if you real code is doing something similar, it needs to be rewritten.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
..110% agreed sir!!! No one would think of writing such an useless code.I just wanted to know if a piece of code works on SQL Server 2000 box what is going wrong in SQL Server 2005? Is it bug in 2005? 
Regards
Shrikant Kulkarni -
Heh... I don't know and I'm sorry about that. I don't have 2l5, yet.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply