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.

2

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).

6

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.