Microsoft KB Archive/252405

From BetaArchive Wiki

Article ID: 252405

Article Last Modified on 11/4/2003


  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 6.5 Service Pack 1
  • Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q252405


When you open an ActiveX Data Objects (ADO) recordset with pessimistic locking on already locked data and then check the recordset for the EOF property, you receive different results on Microsoft SQL Server 6.5, Microsoft SQL Server 7.0, and Microsoft SQL Server 2000. With Microsoft SQL Server 6.5, a time-out error is thrown at the ADO recordset Open call. When you use Microsoft SQL Server 7.0 or Microsoft SQL Server 2000, the Open call is successful and EOF checking returns True and no error is thrown.


This discrepancy occurs because the computer running SQL Server 6.5 is not sending the Tabular Data Stream (TDS) packets back the same way that the computer running SQL Server 7.0 is. As a result, the provider is reacting accordingly.

When you execute the rs.Open call, SQL Server 6.5 makes two Netlibrary API calls together; one to sp_cursoropen, and the other to collect metadata for the returning rowset. The sp_cursoropen call comes back immediately (and successfully) but the following call to get metadata is blocked on the server and the connection times out. Because these two calls are executed as a unit, then the entire rs.Open call fails and the time-out error is returned.

With SQL Server 7.0, these two calls are also issued together, but the call to get metadata is not blocked on the server because it is more optimized in this regard, and doesn't block the metadata. In addition, because they both execute successfully together, then the call to rs.Open completes without error. When EOF is called, ADO does no error checking on whether the data is actually locked in this method, and thus no error is raised. ADO is satisfied until it actually has to retrieve the data with a call to sp_cursorfetch and it finds that the records are locked, and then the error is thrown.

Any back-end server that can separate cursor creation and metadata from the actual data retrieval usually displays the same behavior on EOF calls as that of SQL Server 7.0.


Because EOF is not the place to return errors, you need to either employ a MoveFirst method, or directly reference the recordset so you can detect the lock on the data.

One recommendation is to check rs.Status (which internally calls MoveFirst), because it returns the time-out error if there is one, and barring time-out errors, also truthfully notifies you if EOF/BOF returns True, and gives you the status of the recordset.


This behavior is by design.


Regardless of if you are using the OLE DB Provider or the ODBC driver, the behavior is identical.

Steps to Reproduce Behavior

Add the following code to a Visual Basic project to reproduce the error:

Note You must change the User ID=<username> value and the Password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Dim cnRead As New ADODB.Connection, cnWrite  As New ADODB.Connection
    Dim rsRead As New ADODB.Recordset, rsWrite As New ADODB.Recordset
    Dim strConn As String, strSQL As String
    strConn = "Provider=SQLOLEDB;Data Source=SQLServer1;Initial Catalog=Pubs;User ID=<username>;Password=<strong password>;"
    'strConn = "Provider=MSDASQL;Driver={SQL Server};Server=SQLServer1;Database=Pubs;UID=<username>;PWD=<strong password>;"
    cnRead.CommandTimeout = 12
    cnRead.Open strConn
    cnWrite.Open strConn
    strSQL = "SELECT * FROM Authors WHERE Au_ID = '341-22-1782'"
    rsWrite.Open strSQL, cnWrite, adOpenKeyset, adLockPessimistic, adCmdText
    rsWrite.MoveFirst  '  This locks the data
    'open the same table with adLockPessimistic again
    rsRead.Open strSQL, cnRead, adOpenKeyset, adLockPessimistic, adCmdText 
    'SQL6.5 server, Open will fail with time-out error
    If MsgBox("Check EOF?", vbYesNo) = vbYes Then
        If rsRead.EOF Then   ' SQL7.0 hangs here for time-out period
            MsgBox "No Data, No Error"
            MsgBox "Not EOF", , rsRead!Au_ID
        End If
        MsgBox rsRead!Au_ID
    End If


(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Rick Anderson, Microsoft Corporation.

Keywords: kbdatabase kbprb KB252405