Archive for February, 2014

Mapping Report Subscriptions to SQL JOB Names

February 19, 2014

SELECT ‘EXEC ReportServer.dbo.AddEvent @EventType=”TimedSubscription”,
@EventData=”’
+ CAST(a.SubscriptionID AS VARCHAR(40)) + ”” AS ReportCommand ,
b.name AS JobName ,
a.SubscriptionID ,
e.name ,
e.path ,
d.description ,
laststatus ,
eventtype ,
LastRunTime ,
date_created ,
date_modified
FROM ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS VARCHAR(1000)) = b.name
JOIN ReportServer.dbo.ReportSchedule c ON b.name = c.ScheduleID
JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e ON CAST(d.report_oid AS VARCHAR (MAX)) = CAST(e.itemid AS VARCHAR(MAX))