Carlo Romagnano (11/15/2010)
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