FIX: Problems with Server Cursors on a Stored Procedure

ID: Q175126

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 17338 (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. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

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

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

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.

Additional query words: responsive spinlock rdo keyset work table


Keywords          : kbusage SSrvGPF kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 21, 1999