Microsoft KB Archive/151765

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

Article ID: 151765

Article Last Modified on 10/3/2003

-

APPLIES TO


 * 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:


 * 1) Generate Stored Procedures for Prepared Statements option checkbox in the ODBC SQL Server Driver Setup dialog box is checked.
 * 2) SQL_USE_PROCEDURE_FOR_PREPARE option in the SQLSetConnectOption function is set to SQL_UP_ON.
 * 3) 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.