BUG: Unexpected Cursor Behavior with Set NoCount Statement and SQLOLEDB

• 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

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:




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