If we receive an email with the tite stating - Daily Report Status
and if we see the 2nd part of the email which is
/Golftown Reporting/Merchandising/Buyer Style Performance./Planning Style Performance - Planning Style Performance By Dept
shows status of Pending or Failure
Then run the following query on GT SQL 02 -
use ReportServer
--***
SELECT
c.Path, -- Report Path
'execute msdb.dbo.sp_start_job '''+convert(varchar(MAX),rs.ScheduleID)+'''' as Start_Job_Cmd,
s.[SubscriptionID] -- Subscription ID
,s.[OwnerID] -- Report Owner
,s.[Report_OID] -- Report ID
,rs.ScheduleID as SQLJobName -- Name of Job on SQL Server
,s.[Description] -- Description of the report subscription
,s.[LastStatus] -- Status of last subscription execution.
,s.[EventType] -- Subscription type
,s.[LastRunTime] -- Last time subscription executed
,s.[Parameters] -- Parameters used for subscription
,s.[DeliveryExtension] -- How to deliver the subscription
FROM [ReportServer].[dbo].[Subscriptions] as s
left join [ReportServer].dbo.Catalog as c on c.ItemID = s.Report_OID
left join [ReportServer].dbo.ReportSchedule as rs on rs.ReportID = s.Report_OID
where s.[LastStatus]='Pending'
---order by c.path
union all
SELECT
c.Path, -- Report Path
'execute msdb.dbo.sp_start_job '''+convert(varchar(MAX),rs.ScheduleID)+'''' as Start_Job_Cmd,
s.[SubscriptionID] -- Subscription ID
,s.[OwnerID] -- Report Owner
,s.[Report_OID] -- Report ID
,rs.ScheduleID as SQLJobName -- Name of Job on SQL Server
,s.[Description] -- Description of the report subscription
,s.[LastStatus] -- Status of last subscription execution.
,s.[EventType] -- Subscription type
,s.[LastRunTime] -- Last time subscription executed
,s.[Parameters] -- Parameters used for subscription
,s.[DeliveryExtension] -- How to deliver the subscription
FROM [ReportServer].[dbo].[Subscriptions] as s
left join [ReportServer].dbo.Catalog as c on c.ItemID = s.Report_OID
left join [ReportServer].dbo.ReportSchedule as rs on rs.ReportID = s.Report_OID
where (s.[LastStatus] like '%Failure%' )
---or s.[LastStatus] like '%Error%')---No Data
Copy the execute command from the results and run the execute command