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

• Microsoft SQL Server, version 6.5

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:

1. Generate Stored Procedures for Prepared Statements option checkbox in

1. SQL_USE_PROCEDURE_FOR_PREPARE option in the SQLSetConnectOption function

and 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.