Article ID: 175126
Article Last Modified on 8/10/2006
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q175126
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 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:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
For more information, contact your primary support provider.
MORE INFORMATION
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: kbbug kbfix kbusage KB175126
