BUG: Unexpected Cursor Behavior with Set NoCount Statement and SQLOLEDB

ID: Q235566

The information in this article applies to:

SYMPTOMS

If you try to open a server-side ADO recordset with the "Set NoCount" statement, you would always get ForwardOnly recordset.

This behavior is specific to the OLEDB Provider for SQL Server "SQLOLEDB."


RESOLUTION

  • Use the OLEDB Provider for ODBC "MSDASQL."


    • -or-

  • Open the ADO recordset on the Client Side (Static cursor).


    • -or-

  • Remove the "Set NoCount" statement from your SQL string or stored procedure.



    • STATUS

    Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.


    MORE INFORMATION

    When opening a recordset on the server-side, you would expect the following results:

    Cursor Type Lock Type Expected Cursor
    Static Read-Only Static
    Static Optimistic Dynamic
    Dynamic Any Lock Type Dynamic


    Steps to Reproduce Behavior

    1. Start a new project in Visual Basic. Form1 would be the default form.


    2. Add a Command Button to Form1 (Command1 by default).


    3. Add a reference to "Microsoft ActiveX Data Objects 2.x Library."


    4. Paste the following code to the General Declaration section of Form1:


      5. 
Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL as String

Private Sub Command1_Click()

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open "Provider=SQLOLEDB;User ID=sa;Password=;data source=<Your SQL Server>;Initial Catalog=pubs"
sSQL = "SET NOCOUNT ON Select * From Authors"
rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic

If rs.CursorType = adOpenForwardOnly Then
    MsgBox "You have opened forward-only recordset!"
End If

rs.Close
cn.Close

End Sub
    5. Press the F5 key to run the project. You will get the "forward-only recordset" message box.


    © Microsoft Corporation 1999, All Rights Reserved.
    Contributions by Ammar Abuthuraya, Microsoft Corporation

    Additional query words: 

    
Keywords          : kbADO kbOLEDB200bug kbVBp kbGrpVBDB kbGrpMDAC kbDSupport 
Version           : WINDOWS:2.1,6.0,7.0,7.01
Platform          : WINDOWS 
Issue type        : kbbug

    Last Reviewed: July 14, 1999