Discussion Locked

SQL Server 7.0: cursor errors

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I was wondering if someone out there could help me with the following SQL message I haven't been able to get to the bottom of:

Server: Msg 16915, Level 16, State 1, Procedure procedure_name, Line 54
A cursor with the name 'procedure_name' already exists.
Server: Msg 16905, Level 16, State 1, Procedure procedure_name, Line 58
The cursor is already open.

I've always checked that the cursor has been closed AND deallocated and at one stage I thought it could be a result of fetching too many variables in a cursor but it can even happen when it's only fetching one variable .. and the stored proc still appears to run after this message appears.

Thanks, any help would be appreciated!!

5 replies

by amwalsh

Once a cursor is open it will remain open after the procedure completes unless specifically closed. The proc must include error handling that closes the cursor. I've usually run into this when first writing the proc and omitting the DEALLOCATE CursorName call.

The short answer is to open Query Analyzer and type DEALLOCATE CursorName, then run it. Now run the proc, you should be OK.

by hilary

I close and then deallocate the cursor after ending it i.e. after fetching the last row, and I still get the error.

by chrandrob

My first guess would be that the procedure failed or stopped before gettint to the close cursor call and the next time you ran it it got an error.

This would only happen immediately after the procedure failed. If this error occurs every time, including the first time you run it, my second guess would be that the open cursor call is positioned inside of a loop or multiple conditional statements.

If you could post the stored procedure or email it to me, I could tell you exactly what the problem is.

This discussion is closed to new replies.