Microsoft KB Archive/283845

= PRB: RDO Rowcount Returns Zero For Large SQL Server Recordsets =

Article ID: 283845

Article Last Modified on 7/15/2004

-

APPLIES TO

 Microsoft Visual Basic 5.0 Enterprise Edition, when used with:  Microsoft SQL Server 7.0 Standard Edition

 Microsoft SQL Server 7.0 Service Pack 1

 Microsoft SQL Server 7.0 Service Pack 2

 Microsoft SQL Server 2000 Standard Edition</li></ul> </li> Microsoft Visual Basic 6.0 Enterprise Edition, when used with:  Microsoft SQL Server 7.0 Standard Edition</li></ul>

 Microsoft SQL Server 7.0 Service Pack 1</li></ul>

 Microsoft SQL Server 7.0 Service Pack 2</li></ul>

 Microsoft SQL Server 2000 Standard Edition</li></ul> </li> Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 3, when used with:  Microsoft SQL Server 7.0 Standard Edition</li></ul>

 Microsoft SQL Server 7.0 Service Pack 1</li></ul>

 Microsoft SQL Server 7.0 Service Pack 2</li></ul>

<ul> <li>Microsoft SQL Server 2000 Standard Edition</li></ul> </li> <li>Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4, when used with: <ul> <li>Microsoft SQL Server 7.0 Standard Edition</li></ul>

<ul> <li>Microsoft SQL Server 7.0 Service Pack 1</li></ul>

<ul> <li>Microsoft SQL Server 7.0 Service Pack 2</li></ul>

<ul> <li>Microsoft SQL Server 2000 Standard Edition</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q283845

<div class="symptoms_section">

SYMPTOMS
When returning large rowsets through Remote Data Objects (RDO) from Microsoft SQL Server, rowsets that contain a large number of records may return a rowcount of 0.

<div class="cause_section">

CAUSE
This occurs if the cursor threshold configuration setting for the SQL Server server has been changed from the default value of -1 to 0 or some other positive value.

Setting the cursor threshold value to 0 forces SQL Server to execute all queries asynchronously. Setting the cursor threshold value to a positive number causes SQL Server to execute asynchronously all queries that return a larger number of rows than the chosen setting. In either case, it is impossible for RDO to determine an accurate rowcount from asynchronous queries.

<div class="resolution_section">

RESOLUTION
Change the cursor threshold configuration setting to a lower value, or to -1.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> <li>Start a new Standard EXE project in Visual Basic.</li> <li>On the Project menu, click References, and then select the Microsoft Remote Data Object 2.0 checkbox.</li> <li>Add a command button to the default form, Form1.</li> <li> Copy and paste the following code into the command button's Click event handler: Dim cn As rdoConnection Dim rs As rdoResultset Set cn = New rdoConnection With cn   .CursorDriver = rdUseServer .Connect = &quot;SERVER=myserver;UID=userid;PWD=password;&quot; & _ &quot;DRIVER={SQL Server};DATABASE=Northwind;&quot; .EstablishConnection End With SQL = &quot;SELECT * FROM ORDERS&quot; Set rs = cn.OpenResultset(SQL, rdOpenKeyset, rdConcurReadOnly, rdExecDirect)

MsgBox rs.RowCount

rs.Close

cn.Close Set rs = Nothing Set cn = Nothing </li> <li>Modify the SERVER, UID, and PWD parameters in the Connect method call as appropriate to connect to your SQL Server server.</li> <li>Run the project, and then click the command button. You should see a message box display that contains the accurate rowcount for the Orders table (over 800 rows on SQL Server 7.0).</li> <li> On the SQL Server server, modify the cursor threshold configuration setting by executing the following statement in the SQL Server Query Analyzer:

sp_configure 'cursor threshold', 0

Next, execute the following statement to reconfigure the server: Reconfigure NOTE: The cursor threshold option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change the cursor threshold option only when Show Advanced Options is set to 1. </li> <li>Rerun the project, and then click the command button again. This time, the rowcount returned in the message box should be &quot;0&quot;.</li></ol>

<div class="references_section">