FIX: Busy connections leave cursors open after call to SQLFreeStmt or SQLCloseCursor
<script type=text/javascript>function loadTOCNode(){}</script>Article ID | : | 253010 |
Last Review | : | August 7, 2006 |
Revision | : | 5.0 |
This article was previously published under Q253010
<script type=text/javascript> var sectionFilter = "type != 'notice' && type != 'securedata' && type != 'querywords'"; var tocArrow = "/library/images/support/kbgraphics/public/en-us/downarrow.gif"; var depthLimit = 10; var depth3Limit = 10; var depth4Limit = 5; var depth5Limit = 3; var tocEntryMinimum = 1; </script> <script src="/common/script/gsfx/kbtoc.js?13" type=text/javascript></script>
SYMPTOMS
<script type=text/javascript>loadTOCNode(1, 'symptoms');</script>
When using the Microsoft SQL Server ODBC driver (sqlsrv32.dll) in a manner that permits multiple concurrent statement handles on a single connection, a busy connection may leave cursors open in the SQL Server database with no warnings or notifications to the user. This happens primarily under circumstances where one or more of the statement handles are working in firehose (Forward-Only, Read-Only cursor) mode.
This can have two rather serious side effects. The first side effect is that cursors can be left open on the database server that consumes memory and may leave locks open against records. The second side effect is that subsequent attempts to execute statements on the non-firehose ODBC statement handles may generate the following error for no apparent reason:
This can have two rather serious side effects. The first side effect is that cursors can be left open on the database server that consumes memory and may leave locks open against records. The second side effect is that subsequent attempts to execute statements on the non-firehose ODBC statement handles may generate the following error for no apparent reason:
SQLState: 24000 [Microsoft][ODBC SQL Server Driver]Invalid cursor state
This behavior occurs with the sqlsrv32.dll driver versions 3.70.0690 (Microsoft Data Access Components [MDAC] 2.1 SP2) and earlier, and with sqlsrv32.dll driver version 3.70.820 (MDAC 2.5/Windows 2000).
CAUSE
<script type=text/javascript>loadTOCNode(1, 'cause');</script>
When the SQL Server ODBC driver is busy with a firehose-mode operation, no other activity can occur on the connection until the operation is complete. This includes the
sp_cursorclose calls the driver issues when calling
SQLFreeStmt or
SQLCloseCursor.
When the driver fails to close a cursor, SQLFreeStmt and SQLCloseCursor both return SQL_SUCCESS, no errors are posted to ODBC, and no indication of the failure is available to the application or user. However, the cursor remains open in SQL Server and the statement handle still has a cursor identifier associated with it.
When the driver fails to close a cursor, SQLFreeStmt and SQLCloseCursor both return SQL_SUCCESS, no errors are posted to ODBC, and no indication of the failure is available to the application or user. However, the cursor remains open in SQL Server and the statement handle still has a cursor identifier associated with it.
WORKAROUND
<script type=text/javascript>loadTOCNode(1, 'workaround');</script>
You can use these steps to work around the behavior:
1. | Execute all firehose operations on their own connection. |
2. | Make sure all results from firehose operations are completed before closing cursors.NOTE: In a multithreaded environment, this will frequently not be possible. |
3. | Do not use firehose operations. Instead, set the properties of all statement handles so that cursors are generated. This may include setting rowset size to something greater than one, setting SQL_ATTR_CURSOR_TYPE to something other than SQL_CURSOR_FORWARD_ONLY, and/or setting SQL_ATTR_CONCURRENCY to something other than SQL_CONCUR_READ_ONLY. |
STATUS
<script type=text/javascript>loadTOCNode(1, 'status');</script>
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This problem was corrected in MDAC 2.6.
This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site at:
This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site at:
http://www.microsoft.com/downloads/details.aspx?familyid=F62F45E9-24ED-4FA6-BD74-8A26606F96D8
(http://www.microsoft.com/downloads/details.aspx?familyid=F62F45E9-24ED-4FA6-BD74-8A26606F96D8)
For more information, contact your primary support provider.
MORE INFORMATION
<script type=text/javascript>loadTOCNode(1, 'moreinformation');</script>
Note that this "silent failure" behavior occurs only when the application is attempting to close a cursor at the same time the connection is busy with a firehose operation. Operations other than
sp_cursorclose generally return the following error message:
This hotfix contains changes that permit an application to detect and trap for this situation. SQLFreeStmt and SQLCloseCursor have been modified to return SQL_SUCCESS_WITH_INFO when the sp_cursorclose call failed to execute. Trapping for this return code allows the application to retry the SQLCloseCursor/ SQLFreeStmt call until it succeeds.
NOTE: As an additional check, the UPDLOCK hint can be added to the first statement (the cursor-generating statement), and
sp_lock can then be run in Query Analyzer to show locks are still being held even after the call to
SQLCloseCursor and
SQLFreeStmt. This must be checked before the call to
SQLDisconnect because
SQLDisconnect frees the locks open on the connection.
SQLState: HY000 [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
It is possible to call
SQLFreeStmt or
SQLCloseCursor again after the firehose statement has been closed, and this permits the call to
sp_cursorclose to go through. However, doing so generally requires that the application have knowledge of the fact that the cursor did not close in the first place, and therefore does not constitute a good workaround.
This hotfix contains changes that permit an application to detect and trap for this situation. SQLFreeStmt and SQLCloseCursor have been modified to return SQL_SUCCESS_WITH_INFO when the sp_cursorclose call failed to execute. Trapping for this return code allows the application to retry the SQLCloseCursor/ SQLFreeStmt call until it succeeds.
Steps to Reproduce Behavior
<script type=text/javascript>loadTOCNode(2, 'moreinformation');</script>1. | Copy the following code into a console application and then compile it. Please note that you need to change your ODBC datasource name, user id and password. The datasource connects to the pubs database in SQL Server.
| ||||||||||
2. | Start SQL Profiler, and then connect to the same server to which the console application connects. Make sure that the following events are being monitored in SQL Profiler:
| ||||||||||
3. | Run the code and examine the output in SQL Profiler. Notice that after the sp_prepare and sp_execute on the second statement handle, there is no call to sp_cursorclose even though both SQLCloseCursor and SQLFreeStmt have been called for that statement handle. | ||||||||||
4. | Also notice that no CursorClose event has been triggered. |