How To Access and Retrieve the SQL QueryPlan from RDO (181853)

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