cursor error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

cursor error

Author

Topic

jingjing28
Starting Member

9 Posts

Posted - 2008-10-13 : 11:55:41

I wrote a stored procedure which uses two cursors for looping. the second cursor @typeCursor loops inside the first cursor loop.

I tested the s.p. in dev enviornment without any issue, but got the following error in production:

The variable '@typeCursor' does not currently have a cursor allocated to it

does anyone know why this happened? and why it just happens in production server?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 11:58:41

without seeing code dont think we cant suggest much. can you post code batch?

jingjing28
Starting Member

9 Posts

Posted - 2008-10-13 : 12:09:03

this is the code snippet. let me know if it helps or not:

SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select distinct nb_opus_item_uw.dbo.oritems.orderno
from nb_opus_item_uw.dbo.OrItems
Inner JOIN nb_opus_item_uw.dbo.OrHeader on nb_opus_item_uw.dbo.OrItems.OrderNo=nb_opus_item_uw.dbo.OrHeader.Orderno
where nb_opus_item_uw.dbo.OrItems.status = 'R'
and (nb_opus_item_uw.dbo.OrItems.itemtype = 'File' or nb_opus_item_uw.dbo.OrItems.itemtype = 'Box')
and (nb_opus_item_uw.dbo.OrItems.Plocid in (SELECT PL.id from nb_opus_admin.dbo.Ploc as PL where PL.locname like '%-IM'))
and (nb_opus_item_uw.dbo.OrItems.itemid in (select ID from nb_opus_item_uw.dbo.item where nb_opus_item_uw.dbo.item.typeid in ('1970', '1973', '1922', '1358', '1484', '1485', '1486', '1487', '1493', '1869','2261','1894','2190','2191','2192','1870','2117','2262','2193','2194','1693','2205','1694' )))

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColA

--Loop through each order
WHILE @@FETCH_STATUS = 0
BEGIN

--get orItems for this order
SET @typeCursor = CURSOR FAST_FORWARD
FOR
--select a.typeid from nb_opus_item_uw.dbo.item a, nb_opus_item_uw.dbo.OrItems b where b.orderno=@ColA and a.id=b.itemid
select distinct b.itemid from nb_opus_item_uw.dbo.OrItems b where b.orderno=@colA

open @typeCursor
fetch next from @typeCursor
into @ColB

while @@fetch_status =0
begin

select @tempTp=a.typeid from nb_opus_item_uw.dbo.item a where a.id=@colB

--check if it's a SP order or not
if @tempTp not in ('1970', '1973', '1922', '1358', '1484', '1485', '1486', '1487', '1493', '1869' )
begin
--code goes here

end

end

FETCH NEXT FROM @MyCursor
INTO @ColA
END

CLOSE @MyCursor
DEALLOCATE @MyCursor

close @typeCursor
deallocate @typeCursor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 12:44:48

seems like you really dont need cusror here. what exactly are you trying to do here. can you explain in words?

jingjing28
Starting Member

9 Posts

Posted - 2008-10-13 : 13:54:16

1.get all the orders meeting criteria
2. get each items inside each order
3. do things on the item level.

is there a way to do it without cursors?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 14:45:51

[code]select a.typeid from nb_opus_item_uw.dbo.OrItems b, nb_opus_item_uw.dbo.item a where
a.id = b.itemid and a.typeid not in ( '1970', '1973', '1922', '1358', '1484', '1485', '1486', '1487', '1493', '1869' )
b.orderno in (
Select distinct nb_opus_item_uw.dbo.oritems.orderno
from nb_opus_item_uw.dbo.OrItems
Inner JOIN nb_opus_item_uw.dbo.OrHeader on nb_opus_item_uw.dbo.OrItems.OrderNo=nb_opus_item_uw.dbo.OrHeader.Orderno
where nb_opus_item_uw.dbo.OrItems.status = 'R'
and (nb_opus_item_uw.dbo.OrItems.itemtype = 'File' or nb_opus_item_uw.dbo.OrItems.itemtype = 'Box')
and (nb_opus_item_uw.dbo.OrItems.Plocid in (SELECT PL.id from nb_opus_admin.dbo.Ploc as PL where PL.locname like '%-IM'))
and (nb_opus_item_uw.dbo.OrItems.itemid in (select ID from nb_opus_item_uw.dbo.item where nb_opus_item_uw.dbo.item.typeid in ('1970', '1973', '1922', '1358', '1484', '1485', '1486', '1487', '1493', '1869','2261','1894','2190','2191','2192','1870','2117','2262','2193','2194','1693','2205','1694' )))
)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 14:58:22

It depends on what "do things on the item level" means for you and how well you explain it to us.

E 12°55'05.63"
N 56°04'39.26"

jingjing28
Starting Member

9 Posts

Posted - 2008-10-13 : 15:02:34

but my question is:
why the same code works in dev without any issue, but got error in production? it's not always getting error in prod, but got the error about half time runs.

this was the error:
The variable '@typeCursor' does not currently have a cursor allocated to it

any idea? thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 15:08:49

I am not used to the syntax you use to create the CURSORs.
Are you really using Microsoft SQL Server 2000?

CURSORs are by default global. Do the CURSOR get closed by someone else?

E 12°55'05.63"
N 56°04'39.26"

jingjing28
Starting Member

9 Posts

Posted - 2008-10-13 : 15:44:32

sorry, I am using SQL 2000.

jingjing28
Starting Member

9 Posts

Posted - 2008-10-13 : 15:44:49

sorry, I am using SQL 2003.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-13 : 18:51:58