Can you please explain why
select * from sys.dm_exec_sql_text (sql_handle) throws an error (Invalid column name 'sql_handle'), but
select * from sys.sysprocesses cross apply sys.dm_exec_sql_text (sql_handle) is a valid query? Thank you.
1 Answer
You need to join it to another table to get the sql_handle (or plan_handle).
For example:
select a.session_id, a.start_time, status, a.command, text from sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(sql_handle). sys.dm_exec_sql_text is a table valued function, it expects the parameter sql_handle or plan_handle to be passed to it in order to return a result as other functions do. The result returned is a table (rather than a scalar function which would return a single value).
