FIX: Drop Procedure Gives 3702 Error if Cursor Not Deallocated

• Microsoft SQL Server, version 6.0

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 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.