DOCUMENT:Q181853  27-FEB-1998  [vbwin]
TITLE   :HOWTO: Access and Retrieve the SQL QueryPlan from RDO
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:APrgData

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

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
---------------------------------------------------------------------

SUMMARY
=======

Many relational database management systems (RDBMS) use the query plan to
optimize the path a query takes to achieve the best performance. This
article demonstrates how to retrieve the query plan from SQL Server using
Remote Data Object (RDO) through Visual Basic.

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

Showplan results are returned as individual low severity errors. In order
to access these it is necessary to loop through the Errors collection and
print out the information.

NOTE: By default, the query plan is returned in reverse order as generated.
The code below illustrates how to turn on and view the query plan from
Visual Basic.

HOW TO RETRIEVE THE QUERY PLAN
------------------------------

1. Start a new Visual Basic Standard .exe project.

2. Add a reference to Microsoft Remote Data Object 2.0.

3. Add the following code to the default form and run the project. You will
   see the query plan information printed on the form. Here is the code:

      Private Sub Form_load()
      Dim en As rdoEnvironment
      Dim cn As rdoConnection
      Dim rs As rdoResultset
      Dim strConnect As String
      Dim strSQL As String
      Set en = rdoEngine.rdoEnvironments(0)
      en.CursorDriver = rdUseNone
      strConnect = "Driver={SQL Server};Server=YourServerName;Uid=sa;Pwd="
      Set cn = en.OpenConnection(dsName:="", _
      Prompt:=rdDriverNoPrompt, ReadOnly:=False, Connect:=strConnect)
      cn.Execute "Set ShowPlan On", rdExecDirect
      strSQL = "select * from sysobjects"
      Set rs = cn.OpenResultset(strSQL)
      Me.Show
      For i = 0 To rdoErrors.Count - 1
        Me.Print rdoErrors.Item(i).Description
      Next
      End Sub

REFERENCES
==========

For information on interpreting the output, please refer to the SQL
documentation.

Additional query words: queryplan show plan
======================================================================
Keywords          : APrgData
Version           : WINDOWS:5.0
Platform          : WINDOWS
Issue type        : kbhowto

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

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