Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 235566 - Last Review: May 8, 2003 - Revision: 2.1
BUG: Unexpected Cursor Behavior with Set NoCount Statement and SQLOLEDB
This article was previously published under Q235566
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:
Collapse this tableExpand this table
| Cursor Type | Lock Type | Expected Cursor |
|---|
| Static | Read-Only | Static |
| Static | Optimistic | Dynamic |
| Dynamic | Any Lock Type | Dynamic |
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=<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
- Press the F5 key to run the project. You will get the "forward-only recordset" message box.
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
| kbbug kbdatabase kbnofix kbprovider KB235566 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate