PRB: ADO EOF Returns True on SQL Server 7.0, Returns Time-out Error on SQL Server 6.5 (252405)

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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.

MORE INFORMATION

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 =<strong 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"
        Else
            MsgBox "Not EOF", , rsRead!Au_ID
        End If
    Else
        MsgBox rsRead!Au_ID
    End If