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

• Microsoft Data Access Components versions 2.1, 2.5
• Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0

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.

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.

MORE INFORMATION

Steps to Reproduce Behavior

1. In Visual Basic 5.0 or 6.0, open a new Standard EXE project.



2. From the Project menu select the References menu to add a Reference to Microsoft ActiveX Data Objects 2.1.



3. Paste the following code into the Form1 Code window.




Option Explicit
Const strConnect = "Provider=MSDASQL;Driver={SQL Server};Server=<Server Name>;Database=Pubs;Uid=Sa;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 

4. Run the Project and click on the Form.

REFERENCES

For more information see SQL Books online, and search on "dbcursoropen".

Additional query words: kbAdo

Keywords : kbADO kbOLEDB KbVBA kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC250
Version : WINDOWS:2.1,2.5,5.0,6.0
Platform : WINDOWS
Issue type : kbprb