BUG: Problems with Server Cursors on a Stored Procedure

• Microsoft SQL Server version 6.5

SYMPTOMS

Running a server cursor on a stored procedure may cause SQL Server to stop responding to all clients if all of the following conditions are true:

• The cursor type is either KEYSET or INSENSITIVE.
• Sp_recompile was run on a table that the stored procedure references.
• Blocking exists on the system tables of tempdb.
• Another stored procedure that uses worktables is running simultaneously.

WORKAROUND

To work around this problem, do any of the following:

• Do not use sp_recompile on the dependent table.
• Change the cursor type to DYNAMIC or FORWARD-ONLY.
• Create the stored procedure that is run by the cursor with the WITH RECOMPILE option.
• Run SQL Server with trace flag 7502.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

To determine the type of cursor being used, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q156489
   TITLE     : INF: Overview of SQL Server, ODBC, and DB-Library Cursors

Additionally, running sp_recompile is not necessary, but instead a change to the schema_ver column of the sysobjects system table can cause this problem. The following system stored procedures also modify the schema_ver column: sp_bindefault, sp_bindrule, sp_rename, sp_unbindefault, and sp_unbindrule.