Microsoft KB Archive/252405

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

Article ID: 252405

Article Last Modified on 11/4/2003

-

APPLIES TO


 * 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



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.



STATUS
This behavior is by design.



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= 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= ;Password= ;" 'strConn = "Provider=MSDASQL;Driver={SQL Server};Server=SQLServer1;Database=Pubs;UID= ;PWD= ;" 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

