Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    2

    Red face Unanswered: ShowPlan_All input

    Is anobody know how to return ShowPlan_ALL output to SQL 2000 stored procedure?
    I want to estimate some parameters of SQL statement and then to process estimation results in my stored procedure.
    Thanks a lot for your attention.
    Alexander

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: ShowPlan_All input

    From BOL...

    Using SET SHOWPLAN and SET STATISTICS
    The Transact-SQL SET statement options STATISTICS TIME and STATISTICS IO are used to get information that aids in diagnosing long-running queries. Earlier versions of Microsoft® SQL Server™ also support the SHOWPLAN option for analyzing query plans. An ODBC application can set these options by executing the following statements:

    SQLExecDirect(hstmt, "SET SHOWPLAN ON", SQL_NTS);
    SQLExecDirect(hstmt, "SET STATISTICS TIME ON", SQL_NTS);
    SQLExecDirect(hstmt, "SET STATISTICS IO ON", SQL_NTS);

    When SET STATISTICS TIME or SET SHOWPLAN are ON, SQLExecute and SQLExecDirect return SQL_SUCCESS_WITH_INFO, and, at that point, the application can retrieve the SHOWPLAN or STATISTICS TIME output by calling SQLGetDiagRec until it returns SQL_NO_DATA. Each line of SHOWPLAN data comes back in the format:

    szSqlState="01000", *pfNativeError=6223,
    szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
    Table Scan"

    SQL Server version 7.0 replaces the SHOWPLAN option with SHOWPLAN_ALL and SHOWPLAN_TEXT, both of which return output as a result set, not a set of messages.

    Each line of STATISTICS TIME comes back in the format:

    szSqlState="01000", *pfNativeError= 3613,
    szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
    SQL Server Parse and Compile Time: cpu time = 0 ms."

    The output of SET STATISTICS IO is not available until the end of a result set. To get STATISTICS IO output, the application calls SQLGetDiagRec at the time SQLFetch or SQLFetchScroll returns SQL_NO_DATA. The output of STATISTICS IO comes back in the format:

    szSqlState="01000", *pfNativeError= 3615,
    szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
    Table: testshow scan count 1, logical reads: 1,
    physical reads: 0."

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    2
    Brett,thanks a lot for your answer!
    But my problem is: I want to estimate
    the features of some queries(cost,row counts) BEFORE their execution.
    And,from other side, I want to get this results from my stored procedure).
    So,I have several dynamic statements and i have to select and run one of them.

    Thanks and regards
    Alexander.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •