Post reply

Query text during caching in SQL 2005

  • November 15, 2010 at 9:53 am

    #1250832

    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

  • November 15, 2010 at 2:50 pm

    #1251011

    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.

  • November 15, 2010 at 2:55 pm

    #1251014

    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?

  • November 15, 2010 at 3:30 pm

    #1251036

    Thaks for the question.

  • November 15, 2010 at 3:31 pm

    #1251037

    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]

  • November 15, 2010 at 9:24 pm

    #1251080

    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

  • November 15, 2010 at 10:07 pm

    #1251086

    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

  • November 15, 2010 at 10:09 pm

    #1251089

    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

  • November 15, 2010 at 10:10 pm

    #1251090

    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

  • November 24, 2010 at 11:14 am

    #1255228

    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

  • May 24, 2011 at 8:29 am

    #1329103

    Good Question 🙂

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

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