Microsoft KB Archive/181853

= How To Access and Retrieve the SQL QueryPlan from RDO =

Article ID: 181853

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q181853



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
 Start a new Visual Basic Standard .exe project. Add a reference to Microsoft Remote Data Object 2.0.  Add the following code to the default form and run the project. You will see the query plan information printed on the form. You must have the appropriate permissions and UserID and Password values. 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=UserName;Pwd=StrongPassword" 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



