As you'd expect, there’s a table in the ReportServer database called dbo.Subscriptions, where LastStatus of the Subscription is stored. And based on ScheduledId column which is connected to Job, we can use T-SQL statement to find the subscription which is
failed, and then execute the correspond jobs.
In order to return the failed subscriptions, please refer to the following query in SQL Server Management Studio.
DECLARE @ScheduleId NVARCHAR (50) SET @ScheduleId = (SELECT rs.ScheduleID
FROM ReportServer.dbo.Catalog c WITH(NOLOCK)
INNER JOIN ReportServer.dbo.Subscriptions sub WITH(NOLOCK) ON (c.ItemID = sub.Report_OID)
INNER JOIN ReportServer.dbo.ReportSchedule rs WITH(NOLOCK) ON (c.ItemID = rs.ReportID AND sub.SubscriptionID = rs.SubscriptionID)
INNER JOIN ReportServer.dbo.Schedule sch WITH(NOLOCK) ON (rs.ScheduleID = sch.ScheduleID)
INNER JOIN msdb.dbo.sysjobs sj WITH(NOLOCK) ON (cast(rs.ScheduleID as sysname)=sj.name)
INNER JOIN msdb.dbo.sysjobschedules sjs WITH(NOLOCK) ON (sj.job_id = sjs.job_id)
INNER JOIN msdb.dbo.sysschedules ss WITH(NOLOCK) ON (sjs.schedule_id = ss.schedule_id)
WHERE (sub.LastStatus = 'Done: 1 processed of 1 total; 1 errors.'
OR sub.LastStatus LIKE 'Failure sending mail%')
EXEC msdb..sp_start_job @job_name = @ScheduleId
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.