Resend Reporting Services Email Subscriptions

Troubleshooting Microsoft Reporting Services email subscriptions can be a pain. How many times have you created a new subscription a few minutes in advance and not received it, then you are unsure if you set the schedule time correctly, or if you pressed the save button, or if the fix you made to reporting services didn’t work? It’s a time consuming process, but thankfully, there is a better way.

Instead, you can run some SQL commands on your reporting services to trigger the running and resending of this report.

• Use SQL Server Management Studio and connect to the database engine of your reports server
• Click the New Query button
• Run this SQL query to list of all the reports with schedules:

SELECT
sj.[name] AS [Job Name],
c.[Name] AS [Report Name],
c.[Path],
su.Description,
su.EventType,
su.LastStatus,
su.LastRunTime
FROM msdb..sysjobs AS sj INNER JOIN ReportServer..ReportSchedule AS rs
ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) INNER JOIN
ReportServer..Subscriptions AS su
ON rs.SubscriptionID = su.SubscriptionID INNER JOIN
ReportServer..[Catalog] c
ON su.Report_OID = c.ItemID

• From the Results pane, determine the job name of the report you want to trigger. Right click on the the job Guid in the JobName column and click copy

• Click the New Query button again to open a blank query window
• Run this SQL query, replacing YourJobNameHere with your Job Name retrieved from the last step

USE [msdb]
EXEC sp_start_job @job_name = 'YourJobNameHere'

• When you execute the query, the Message window should say Job 'GUID' started successfully:

If you don’t receive the report - then you know you didn’t fix the initial problem, but now at least, you have a fast way to resend the report each time you change a Reporting Services / SMTP setting!