Wednesday, 29 September 2010

Identify SQL Agent job that runs a Reporting Services subscription

So you want to manually kick off a Reporting Services subscription outside of its usual schedule ... easy you think, I'll just run the SQL Agent job.

Then you find this ...

For some reason, reporting services wants to keep it a secret which job runs which subscription, by using easy to remember GUID based job names.

Fear not ... this SQL will give you a list of the job names against the actual reports

SELECT ScheduleID, Path, Name, s.Description
FROM ReportServer.dbo.Catalog c
JOIN ReportServer.dbo.Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

Just find the job with the same name as the ScheduleId of the report subscription you want, and you can manually start it whenever you like.