DOCUMENT:Q283845  27-JAN-2001  [vbwin]
TITLE   :PRB: RDO Rowcount Returns Zero For Large SQL Server Recordsets
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER::5.0,6.0,6.0 SP3,6.0 SP4,7.0,7.0 Service Pack 1,7.0 Service Pack 2
OPER/SYS:
KEYWORDS:kbRDO kbGrpDSVBDB kbDSupport

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0, 6.0 SP3, 6.0 SP4, used with:
    - Microsoft SQL Server versions 7.0, 7.0 Service Pack 1, 7.0 Service Pack 2 
    - Microsoft SQL Server 2000 (all editions) 
-------------------------------------------------------------------------------

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.

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.

RESOLUTION
==========

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

STATUS
======

This behavior is by design.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

1. Start a new Standard EXE project in Visual Basic.

2. On the Project menu, click References, and then select the Microsoft Remote
   Data Object 2.0 checkbox.

3. Add a command button to the default form, Form1.

4. 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 = "SERVER=myserver;UID=userid;PWD=password;" & _
                  "DRIVER={SQL Server};DATABASE=Northwind;"
       .EstablishConnection
     End With
     
     SQL = "SELECT * FROM ORDERS"
     
     Set rs = cn.OpenResultset(SQL, rdOpenKeyset, rdConcurReadOnly, rdExecDirect)

     MsgBox rs.RowCount

     rs.Close

     cn.Close
     
     Set rs = Nothing
     Set cn = Nothing

5. Modify the SERVER, UID, and PWD parameters in the Connect method call as
   appropriate to connect to your SQL Server server.

6. 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).

7. 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.

8. Rerun the project, and then click the command button again. This time, the
   rowcount returned in the message box should be "0".

REFERENCES
==========

SQL Server Books Online, topic: "Cursor Threshold"

Additional query words:

======================================================================
Keywords          : kbRDO kbGrpDSVBDB kbDSupport 
Technology        : kbVBSearch kbAudDeveloper kbPTNotAssigned kbZNotKeyword2
Version           : :5.0,6.0,6.0 SP3,6.0 SP4,7.0,7.0 Service Pack 1,7.0 Service Pack 2
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.