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