Q138538 - FIX: Drop Procedure Gives 3702 Error if Cursor Not Deallocated

FIX: Drop Procedure Gives 3702 Error if Cursor Not Deallocated

Q138538

The information in this article applies to:

Microsoft SQL Server version 6.0

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 : kbprogramming
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600