BUG: Unexpected Cursor Behavior with Set NoCount Statement and SQLOLEDB

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."

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.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

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

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 into the General Declarations section of Form1. Modify the Connection String to connect to your SQL Server:

   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=username;Password=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 a 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.