Query text during caching in SQL 2005

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    Oleg Netchaev (11/15/2010)


    Carlo Romagnano (11/14/2010)


    In sql2005, I get an error:

    "plan_handle" is not a recognized table hints option.

    Carlo,

    Please check compat level of your SQL Server 2005 AdventureWorks database. If the level is 80 then you should get "plan_handle" is not a recognized table hints option error, but if it is 90 as it should be then the script should run just fine.

    Oleg

    I tried at midnight, maybe, my notebook or I was out of order. 😀

    Thank you

  • mojo-168709

    SSCommitted

    Points: 1825

    Using the code supplied, I got this result:

    -- uspPrintError prints error information about the error that caused -- execution to jump to the CATCH block of a TRY...CATCH construct. -- Should be executed from within the scope of a CATCH block otherwise -- it will return without printing any error information. CREATE PROCEDURE [dbo].[uspPrintError] AS BEGIN SET NOCOUNT ON; -- Print error information. PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + ', State ' + CONVERT(varchar(5), ERROR_STATE()) + ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + ', Line ' + CONVERT(varchar(5), ERROR_LINE()); PRINT ERROR_MESSAGE(); END;

    SELECT st.text QueryText FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'%uspPrintError%';

    The only way to get the "correct" answer the author intended is to remove the "GO statement between EXEC uspPrintError and the SELECT stmt.

    I'm using SQL 2005, compat. level 90.

  • jeff.mason

    SSCrazy Eights

    Points: 9555

    mojo-168709 (11/15/2010)


    Using the code supplied, I got this result:

    -- uspPrintError prints error information about the error that caused -- execution to jump to the CATCH block of a TRY...CATCH construct. -- Should be executed from within the scope of a CATCH block otherwise -- it will return without printing any error information. CREATE PROCEDURE [dbo].[uspPrintError] AS BEGIN SET NOCOUNT ON; -- Print error information. PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + ', State ' + CONVERT(varchar(5), ERROR_STATE()) + ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + ', Line ' + CONVERT(varchar(5), ERROR_LINE()); PRINT ERROR_MESSAGE(); END;

    SELECT st.text QueryText FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'%uspPrintError%';

    The only way to get the "correct" answer the author intended is to remove the "GO statement between EXEC uspPrintError and the SELECT stmt.

    I'm using SQL 2005, compat. level 90.

    I am confused -- the code you posted IS the expected result? CREATE instead of EXEC?

  • UMG Developer

    SSChampion

    Points: 13482

    Thaks for the question.

  • @SQLFRNDZ

    SSCrazy Eights

    Points: 9095

    EXEC uspPrintError

    GO

    I know before this thing but I answered it wrong bcoz

    EXEC uspPrintError

    GO

    what this doing over there.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Oleg Netchaev (11/15/2010)


    Carlo Romagnano (11/14/2010)


    In sql2005, I get an error:

    "plan_handle" is not a recognized table hints option.

    Carlo,

    Please check compat level of your SQL Server 2005 AdventureWorks database. If the level is 80 then you should get "plan_handle" is not a recognized table hints option error, but if it is 90 as it should be then the script should run just fine.

    Oleg

    Thanks for resolving the issue, due to project work not able to answer.

    Thanks

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Hugo Kornelis (11/14/2010)


    Good question. I answered wrong; I thought all statements were cached.

    Hardik, do you have any references where I can read more about this?

    Thanks Hugo. Sorry I don't have material for the same. I learned this when I face similar type of issue (I asked Pinal who is SQL Server MVP). So, thought to share to all 🙂

    Thanks

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Carlo Romagnano (11/15/2010)


    Execute the batch without the 'GO'

    EXEC uspLogError

    SELECT st.text QueryText,objtype

    FROM sys.dm_exec_cached_plans

    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

    WHERE text LIKE N'%uspLogError%'

    Result:

    CREATE PROCEDURE [dbo].[uspLogError] ....,'Proc'

    EXEC uspLogError SELECT st.text QueryText FROM sys.dm_exec_cached_plans ....,'Adhoc'

    The body of the stored procedure is cached in the objtype = 'Proc' (see also other info about the proc). the second record is the plan stored with objtype = 'Adhoc'

    See also: http://msdn.microsoft.com/en-us/library/ms187404.aspx%5B/quote%5D

    Execute the batch with GO - is the real part. thanks for validating & provide to link to all.

    Thanks

  • Hardy21

    SSCrazy Eights

    Points: 9708

    RichardDouglas (11/15/2010)


    Good question, thanks for taking the time to help educate the SQL community.

    I look at these DMV's a lot so thankfully answered this one correctly.

    Good to know.

    Thanks

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • U J

    SSCrazy

    Points: 2423

    Good Question 🙂

Viewing 11 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply