BUG: Unexpected Cursor Behavior with Set NoCount Statement and SQLOLEDB (235566)


The information in this article applies to:

  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft OLE DB Provider for SQL Server 7.0
  • Microsoft OLE DB Provider for SQL Server 7.01
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q235566

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 that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

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

Cursor TypeLock TypeExpected Cursor
StaticRead-OnlyStatic
StaticOptimisticDynamic
DynamicAny Lock TypeDynamic


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.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbBug kbDatabase kbnofix kbProvider KB235566
©2002 Microsoft Corporation. All rights reserved.