Unable to create reports on blocking query & backup status in DPA. Tried with custom SQL query in SAM. But it gives query invalid.
If I use the existing template for blocking query (custom chart), I am unable to get SPID, blocked query details in the report. If I use custom table, it gives only current value not the historic values.
Below is the query which I have tried in SAM & Its throws query invalid error.
select a.session_id ,
b.session_id ,
sql_text.text ,sql_btext.text ,c.wait_type e from sys.dm_exec_requests a
inner join sys.dm_exec_requests b
on a.blocking_session_id = b.session_id
cross apply (select * from sys.dm_exec_sql_text(b.sql_handle)
) sql_btext
cross apply
(select * from sys.dm_exec_sql_text(a.sql_handle)
) sql_text
inner join sys.dm_os_waiting_tasks c
on c.session_id=b.session_id
Could anyone help assist on this ?
