I figured it out, had to set cursor as static.


Using sql 2000

I'm trying to do a query I'm working in QA. When I get set the @tiDocs variable to @@Cursor_rows, it return -1. If I just run the query by itself, I get 148 records. What am I missing?

Thanks

create table #PickList (LastName varchar(40),firstname varchar(20),coPerID int, RoomName varchar(20),isResident bit, isSuspened int, NumCancelled int,NumSched int,specdesc varchar(50))
declare Avail_Docs cursor LOCAL READ_ONLY for
SELECT  dbo.coper.lastname, 
	dbo.coper.firstname, 
	dbo.coper.coperid, 
	' ' as roomname,
	isnull(dbo.coper.isResident,0) as isResident, 
	isnull(dbo.coper.isSuspended,0) as isSuspended,
	0 as Numcancelled,
	0 as NumSched,
	isnull((Select specdesc from dbo.coSpec with (nolock) where coSpec.specID=coPer.SpecID and coSpec.Isactive=1),' ') as specdesc
FROM    dbo.coper INNER JOIN
        dbo.coperdept ON dbo.coper.coperid = dbo.coperdept.coperid
where coPerDept.deptID=@tiDept
and coPer.isactive=1
and coPer.perTypeID=1
order by coper.lastname,coPer.firstname



-- Open our cursor and determine how many rows are in it
Open Avail_Docs
set @tiDocs=@@cursor_rows
print @tiDocs
while (@tiCtr <= @tiDocs)
Begin
	fetch Avail_Docs into @c_LastName,@c_FirstName,@c_coPerID,@c_RoomName,@c_isResident,@c_isSuspended,@c_NumCancelled,@c_NumSched,@c_SpecDesc
	insert into #picklist (LastName,firstname,coPerID,RoomName,isResident, isSuspened, NumCancelled,NumSched,specdesc) values
	             	   (@c_LastName,@c_firstname,@c_coPerID,@c_RoomName,@c_isResident,@c_isSuspended,@c_NumCancelled,@c_NumSched,@c_specdesc)

END

select * from #picklist

Close Avail_Docs
DeAllocate Avail_Docs

drop table #picklist