cursor error msg

Question

Post reply

cursor error msg

Randy Tarrier

Grasshopper

Points: 21
More actions
March 21, 2006 at 3:41 pm

#89899

I'm new to sql server, and I've been searching thru discussions on this site regarding cursors. I'm having one problem in particular, I can run a query in my development environment, but when I put that query in a cursor, then run the script, I get an error message:
OutputQry: Cursor not returned from query
The much abbreviated code is:
BEGIN
Declare @v_case_nbrvarchar( 20)
Declare @v_case_idchar( 20 )
Declare @ProcErrint
err_messchar( 40 )
DECLARE get_cases CURSOR LOCAL for
SELECT rc.case_id
from dbo.REAL_CASE rc
-- Begin Main procedure --
BEGIN
Open get_cases
WHILE ( 0 = 0) Begin
Fetch Next
Into @v_case_id
From get_cases
END
deallocate get_cases
END
I should mention my development environment is a product called "DB_designer", connected to a SQLExpress 2005 instance running on a desktop PC w/Win2k SP4 w/ 225,000 KB RAM
I'm wondering if this is a resource issue, or am I overlooking something really elementary?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

It doesn't appear that the cursor ever stops, so you're running off the end of the result set.

Try

WHILE @@FETCH_STATUS = 0

instead of the 0=0 condition.

0 indicates a successful fetch, anything else should leave the loop.

And then again, I might be wrong ...
David Webb

Randy Tarrier Grasshopper Points: 21 More actions · Answer 2

David, thank you, but I got the same result when I made that change:

-- Begin Main procedure --

BEGIN

Open get_cases

WHILE @@FETCH_STATUS = 0 Begin

Fetch Next

From get_cases

Into @v_case_id

END

deallocate get_cases

END

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

-- Begin Main procedure --

BEGIN

Open get_cases

WHILE @@FETCH_STATUS = 0 Begin

Fetch Next

From get_cases

Into @v_case_id

END

CLOSE get_cases

deallocate get_cases

END

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Ramesh Lende Ten Centuries Points: 1034 More actions · Answer 4

I would say if at possible try to avoid cursors. Think if you can change query so that it can be direct SQL instead of cursor

thanks,

Ramesh.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Since you say your code is "much abbreviated", it's difficult to see where the error could be...here's the syntax that "should" work...

-- Begin Main procedure --
BEGIN
OPEN get_cases
FETCH NEXT FROM get_cases INTO @v_case_id

WHILE @@FETCH_STATUS = 0 
BEGIN
....process.....
FETCH NEXT FROM get_cases INTO @v_case_id
END

CLOSE get_cases 
DEALLOCATE get_cases
END

Also, you say you're new to SQL Server so it may just be in your best interest to post the whole code so the wise folks here could come up with a cursorless solution (if possible)..

**ASCII stupid question, get a stupid ANSI !!!**

Randy Tarrier Grasshopper Points: 21 More actions · Answer 6

Ahh, I think I'm seeing a pattern here; I get this error msg for other things having nothing to do with a cursor in my script.... so it's talking about the query cursor sql server returns... I get this kind of message when I have a space in front of some stmnts- hence the 'go' verb, right?

Still can't get the query to work, getting rid of spaces, but I'm learning some of the quirks ;>), thanks all for your responses!