20 January 2022

How do you find an SSRS subscription’s SQL Agent job?

How do you find an SSRS subscription SQL Agent job

SQL Server Reporting Services (SSRS) uses SQL Server Agent to schedule report subscriptions but the name of the job doesn’t match the subscription, so when you get an error message about a failed subscription you’ll need to run a query to find it.

The Subscription table in the ReportServer database joins, via the SubscriptionId, to the ReportSchedule table and the ScheduleId of a given subscription is the name used to create the SQL Server Agent job. The below query gives us the job_id of the job that is associated with a specific subscription:

select j.job_id
from ReportServer.dbo.Subscriptions s
join ReportSchedule r on r.SubscriptionID = s.SubscriptionID
join msdb.dbo.sysjobs j on j.name = convert(sysname, r.ScheduleID)
where s.SubscriptionID = 'ab35126f-a605-4069-973b-160a59bf36c6';

From there, you can then query msdb for the execution history of the job:

select *
from msdb.dbo.sysjobhistory
where job_id = '4DA64BC9-EEBD-462B-86E3-5781AA6648AE';


SQL Server | SSRS


Let’s Talk

Have a SQL challenge? We can help.

"*" indicates required fields

This field is for validation purposes and should be left unchanged.