DELETE ALL SUBSCRIPTIONS IN REPORTING SERVICES

DELETE FROM ReportSchedule GO DELETE FROM Subscriptions GO DELETE FROM Schedule GO

List of failed SSRS subscription jobs

1. Get all the failed job related to SSRS subscription
SELECT * FROM dbo.Subscriptions WHERE LastStatus LIKE 'Failure%'

2. Get all the failed job related to email subscription

SELECT r.ScheduleID,s.LastStatus,s.Description,c.Name,c.Path,s.LastRunTime FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE s.LastStatus like 'Failure%'
and s.Description like '%e-mail%'
order by s.LastRunTime desc


3. Get all the failed job realted to file write subscription

SELECT r.ScheduleID,s.LastStatus,s.Description,c.Name,c.Path,s.LastRunTime FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE s.LastStatus like 'Failure%'
and s.Description like '%Save%'
order by s.LastRunTime desc


4. Check the execution log

select * from ExecutionLog
select * from ExecutionLog2
select * from ExecutionLog3


5. Check the log file

C:\Program Files\Microsoft SQL Server\MSRS11.JTIME\Reporting Services\LogFiles\ReportServerService__MM_DD_YYYY_hh_mm_ss.log

Running SSRS Subscription job

1. First we need get the ScheduleID from the following query by report name.

SELECT r.ScheduleID,s.LastStatus,s.LastRunTime  FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE c.name='ReportName'

example:

SELECT r.ScheduleID,s.LastStatus,s.LastRunTime  FROM dbo.Subscriptions s

INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID

INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID

WHERE c.name='HSMTaskReport'

2. Then get the job id by ScheduleID (it actually job name). 

select * from msdb.dbo.sysjobs where name='ScheduleID'

example:

select * from msdb.dbo.sysjobs where name='FBC6126C-C038-41DD-B40D-1DC5EE446B39'

3. Then run the job with job id.

exec msdb..sp_start_job @job_id='JOB_ID'

example:

exec msdb..sp_start_job @job_id='3E3A3645-53B6-4577-9D17-941D4B6BFCFD'