How to resend SSRS subscription emails?

Question

If our report email subscriptions fail to get sent due to an issue like the SMTP server fails we have to manually re-send the report emails. Is there any way we can automatically re-fire the subscription emails and re-send them?

Answer 1

I've used a similar approach to what Hannover Fist posted, but with a different approach to re-firing the subscriptions:

SELECT
     S.ScheduleID AS SQLAgent_Job_Name,
     SUB.Description AS Sub_Desc,
     SUB.DeliveryExtension AS Sub_Del_Extension,
     C.Name AS ReportName,
     C.Path AS ReportPath,SUB.LastStatus
FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)

WHERE LEFT (SUB.LastStatus, 12) Like 'Failure%' or LEFT (SUB.LastStatus, 12) Like 'Error%'
      order by reportname

     USE msdb 
    EXEC sp_start_job @job_name = <SQLAgent_Job_Name>

One of the big differences is that mine captures both "Failure" and "Error" statuses.

I wish that I could remember where I found this so that I could give proper credit for it, since it's been a life-saver on a couple of occasions.

Answer 2

There's not an automatic way to do it.

You can get a list of the reports along with the Subscription IDs that failed by querying the Subscriptions table in the ReportServer database. Then JOIN it to the Catalog table to show the report details.

SELECT Sub.SubscriptionID,
       Sub.InactiveFlags,
       Sub.ExtensionSettings,
       Sub.Description,
       Sub.LastStatus,
       Sub.EventType,
       Sub.MatchData,
       Sub.LastRunTime,
       Sub.Parameters,
       Sub.DeliveryExtension,
       Cat.Path,
       Cat.Name,
       Cat.Description,
       Cat.Hidden,
       Cat.Parameter,
       Cat.ExecutionFlag,
       Cat.ExecutionTime
FROM [ReportServer].[dbo].[Subscriptions] as Sub
LEFT OUTER JOIN [ReportServer].[dbo].[Catalog] as Cat on
Sub.Report_OID = ItemID
WHERE LastStatus LIKE 'Failure%'

You could then use the Subscription ID to fire off the subscription for each report using the AddEvent function.

EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;

You could create a stored procedure to loop through the table and fire off the subscriptions.