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.
↑ Back to the top
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.
↑ Back to the top
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.
↑ Back to the top
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
↑ Back to the top
(c) Microsoft Corporation 1999, All Rights Reserved.
Contributions by Rick Anderson, Microsoft Corporation.
↑ Back to the top