Microsoft KB Archive/235052

= PRB: TDS Error with Substring and Multiple ADO Recordsets =

Article ID: 235052

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q235052



SYMPTOMS
When using Microsoft SQL 6.5 and MSDASQL provider with multiple ADO client-side recordsets sharing the same connection, and using a Substring on a column, the following error may appear:

[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream.

If you first open a connection for a recordset that does not contain a Substring, then attempt to open the second recordset containing a Substring where the columns in the Order by clause are not in the same sequence as the Select part of the SQL statement, you will see the TDS error.



CAUSE
The cursor properties on the connection object for the recordset without a Substring with Order By clause are not correct for cursor properties containing an Order By clause with Substring column.



RESOLUTION
One workaround is to simply reference the columns in the Order By clause in the same sequence as the Select part of the SQL statement. Otherwise, open the recordset containing the Substring before any other recordsets sharing the same connection object. Also, you could use another connection for the Substring recordset. This problem does not reproduce in SQL Server 7 or with the SQLOLEDB provider.



Steps to Reproduce the Behavior
 In Visual Basic 5.0 or 6.0, open a new Standard EXE project. From the Project menu select the References menu to add a Reference to Microsoft ActiveX Data Objects 2.x.  Paste the following code into the Form1 Code window.

Note You must change Server=, Uid=, and Pwd= to the appropriate server name, username, and password before you run this code. Make sure that the changed Uid has the appropriate permissions to perform the required operations on the specified database. Option Explicit Const strConnect = "Provider=MSDASQL;Driver={SQL Server};Server=;Database=Pubs;Uid= ;Pwd= "

Private Sub Form_Click

On Error GoTo ErrorHandler Dim adoCn As ADODB.Connection Dim adoRs1 As ADODB.Recordset Dim adoRs2 As ADODB.Recordset Dim strSQL As String

Set adoCn = New ADODB.Connection With adoCn .CursorLocation = adUseClient .ConnectionString = strConnect .CommandTimeout = 20 .Open End With

strSQL = "Select au_lname from Authors"

Set adoRs1 = New ADODB.Recordset With adoRs1 .CursorLocation = adUseClient Set .ActiveConnection = adoCn .CursorType = adOpenStatic .LockType = adLockOptimistic .Open strSQL End With

'strSQL = "Select substring(au_lname, 1, 2) as subLname, " & _ "au_fname, city " & _ "From Authors " & _ "Order By subLname asc, au_fname asc, city asc"

strSQL = "Select substring(au_lname, 1, 2) as subLname, " & _ "au_fname, city " & _ "From Authors " & _ "Order By au_fname asc, subLname asc, city asc"

Set adoRs2 = New ADODB.Recordset With adoRs2 .CursorLocation = adUseClient Set .ActiveConnection = adoCn .CursorType = adOpenStatic .LockType = adLockOptimistic .Open strSQL End With While Not adoRs2.EOF Debug.Print adoRs2.Fields(0).Value adoRs2.MoveNext Wend MsgBox "Success", vbOKOnly, "Data Object" Exit Sub ErrorHandler: MsgBox "Change Failed:" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly, "Data Object" Exit Sub End Sub  Run the Project and click on the Form.</ol>

Comment out the current strSQL used earlier and uncomment the unused SQL statement to correct the problem. Also, you may correct the problem by moving the code to create the Substring recordset (adoRs2) before the code to create adoRs1.

<div class="references_section">