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.


Anonymous said...

Thanks for posting this--this really saves a lot of time figuring out which job is for what schedule (doing it manually).

Rally helpful--makes a grumpy DBA a little less grumpy :)

Anonymous said...

helped a lot

Brad said...

Knew I'd seen this on your blog Uncle Tim ... and now it's come in handy 2 years later! Nice.

Anonymous said...

Thanks a lot friend.. helped a lot.

Anonymous said...

It's because of a *really* bad design flaw. The link between the SQL Agent Jobs and the ReportServer.dbo.Schedule table is via the SysJobs.Name column!!!!

Post a Comment