FIX: Drop Procedure Gives 3702 Error if Cursor Not Deallocated

ID: Q138538

The information in this article applies to:
BUG# NT: 11556 (6.00)

SYMPTOMS

If you execute a stored procedure that declares a cursor not using exec(), and then close the connection before the cursor is opened or deallocated, and then try to drop the procedure from a new connection, a 3702 error similar to the following will be generated:

Msg 3702, Level 16, State 3
Cannot drop the procedure 'cursor3702' because it is currently in use.


WORKAROUND

Declare your cursor using exec(), or make sure that you always open and/or deallocate the cursor before you close your connection. For example: 


   use pubs
   go
   create procedure cursor3702 as
       exec("declare testcursor cursor for
       select * from authors")
   go
   cursor3702
   go

If you now close this connection, you can drop the procedure from a new connection.

If you receive the 3702 error, and the connection which executed the stored procedure is already closed, you will need to restart SQL Server before you can drop the procedure.


STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 2 for SQL Server version 6.0. For more information, contact your primary support provider.

Additional query words: sql6 windows nt sproc 


Keywords          : kbprg SSrvStProc kbbug6.00 kbfix6.00.sp2 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 19, 1999