SQL Query Analyzer

[Ben Ralph]

When I run a stored procedure (that returns the results in a cursor) from SQL Query Analyzer the results are only returned the first time I run the procedure. When I run the procedure, without changing any values, for a second time the cursor is empty. To correct the problem I have to close and restart SQL Query Analyzer to re-run the procedure.

Does anyone know what might be causing this problem?

Cheers
Ben

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tibor Karaszi]

Ben,

Strange... Can you post a repro?

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

"Ben Ralph" <benr...@halifax.co.uk> wrote in message
news:#FB0Ezl8AHA.2120@tkmsftngp02...

[Tony Rogerson]

Are you definitely closing and deallocating the cursor in the stored
procedure?

--
Tony Rogerson SQL Server MVP
Independant Consultant
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK Independant SQL Server User Group; FAQ; KBase
etc..]

"Ben Ralph" <benr...@halifax.co.uk> wrote in message
news:#FB0Ezl8AHA.2120@tkmsftngp02...

[Ben Ralph]

I'm using the following to test my procedure...

DECLARE @v_Results CURSOR

EXEC PR_Orders '01/05/2001','31/05/2001','1','99',@v_Results OUTPUT

PRINT @@ROWCOUNT
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @v_Results
END

CLOSE @v_Results
DEALLOCATE @v_Results
GO

If I close the cursor as part of the procedure that I'm calling I get the following error...

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

Thefore my procedure only opens the cursor.

[Gert E.R. Drapers]

Please post the code of PR_Orders as well.
-GertD

"Ben Ralph" <benr...@halifax.co.uk> wrote in message

news:uQpzP9l8AHA.1884@tkmsftngp05...