Microsoft KB Archive/235566

= BUG: Unexpected Cursor Behavior with Set NoCount Statement and SQLOLEDB =

Article ID: 235566

Article Last Modified on 5/8/2003

-

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 6.0 Enterprise Edition

-



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:

Steps to Reproduce Behavior
 Start a new project in Visual Basic. Form1 would be the default form. Add a Command Button to Form1 (Command1 by default). Add a reference to "Microsoft ActiveX Data Objects 2.x Library."  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=;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  Press the F5 key to run the project. You will get the "forward-only recordset" message box.</li></ol>

Keywords: kbbug kbnofix kbprovider kbdatabase KB235566

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.