Tuesday 23 March 2010

Quick query to list which Reporting Services report the SQL Agent jobs relate to.

SELECT
  jb.[name] as [job_name]
  , cat.[name] as [report_name]
  , cat.[path] as [report_location]
  , sub.[description] as [sub_desc]
  , sub.[LastRunTime], sub.[LastStatus]
FROM (
    select 
      jb.[name]
      , replace(right(stp.[command]
        , charindex('=ataDtnevE@', reverse(stp.[command]))-1)
          , '''', '') as [SubscriptionID]
    from [msdb].[dbo].[sysjobs] jb
      inner join [msdb].[dbo].[sysjobsteps] stp
      on jb.job_id = stp.[job_id]
    where jb.[category_id] = 100
  ) jb
  JOIN [ReportServer].[dbo].[ReportSchedule] sch 
  ON jb.[SubscriptionID] = sch.[SubscriptionID]
    JOIN [ReportServer].[dbo].[Subscriptions] sub
    ON sch.[SubscriptionID] = sub.[SubscriptionID]
      JOIN [ReportServer].[dbo].[Catalog] cat
      ON sub.[report_oid] = cat.[itemid]

No comments: