Oleg Netchaev (11/15/2010)

---

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

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?

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

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.