Mapping Report Subscriptions to SQL JOB Names

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: