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]
Tuesday, 23 March 2010
Quick query to list which Reporting Services report the SQL Agent jobs relate to.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment