Knowledge Base

FIX: SELECT INTO Inside a Temp Sproc Causes Client to Hang

Article ID: 151765

Article Last Modified on 10/3/2003

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q151765

SYMPTOMS

In SQL Server version 6.5, executing a temporary sproc containing a SELECT INTO statement causes the client to stop responding. The server never finishes the execution and the control is not returned to the client. CPU utilization on the server computer goes above 95 percent and persists, with the result that the server slows down drastically. This does not prevent other clients from connecting to SQL Server, but the queries executed from these clients will be slow.

WORKAROUND

Use permanent stored procedure in place of temporary stored procedure. For ODBC clients, clear the Generate Stored Procedures for Prepared Statements option check box in the ODBC SQL Server Driver Setup dialog box, or set the SQL_USE_PROCEDURE_FOR_PREPARE option in the SQLSetConnectOption function to SQL_UP_OFF.

MORE INFORMATION

This behavior is also seen in Microsoft SQL Server 6.0.

With ODBC clients the same behavior is seen if:

Generate Stored Procedures for Prepared Statements option checkbox in the ODBC SQL Server Driver Setup dialog box is checked.
SQL_USE_PROCEDURE_FOR_PREPARE option in the SQLSetConnectOption function is set to SQL_UP_ON.
a SELECT INTO statement is prepared and executed.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

Additional query words: temporary stored procedure

Keywords: kbbug kbfix kbnetwork KB151765