Carlo Romagnano (11/14/2010)

---

Very bad worded question:

Query text during caching in SQL 2005

This is related to SQL Server caching. What should be the output of following T-SQL query:

USE AdventureWorks2008R2

GO

EXEC uspPrintError

GO

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%';

In the title, it refers to sql2005, than it uses AdventureWorks2008R2

In sql2005, I get an error:

"plan_handle" is not a recognized table hints option.

I also tried to run

SELECT text FROM sys.dm_exec_sql_text(0x0500FF7F79BB1808C8808A04000000000000000000000000)

But it returns this:

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

At end I answered BOTH: EXEC ... and text for the stored proc.

The explanation seems to be approximate!