BUG: Unexpected Cursor Behavior with Set NoCount Statement and SQLOLEDB

ID: Q235566


The information in this article applies to:
  • Microsoft Data Access Components version 2.1
  • Microsoft OLE DB Provider for SQL Server, versions 7.0, 7.01
  • Microsoft Visual Basic Enterprise Edition for Windows, version 6.0


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 
    6. 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
    © 2000 Microsoft Corporation. All rights reserved. Terms of Use.