FIX: Problems with Server Cursors on a Stored Procedure

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.

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.

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:For more information, contact your primary support provider.

To determine if your query uses a worktable, see the "Worktable n" topic in SQL Server Books Online. You can find this topic in the Administrator's Companion 6.0 under Part 8, "Troubleshooting" in Chapter 23, "Understanding SHOWPLAN Output."

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.