Automation of failed SSRS subscriptions

As most of the organizations have multiple reports being run on the SSRS reporting servers and it is definite that we create subscriptions to send reports on timely basis and some do fail to be delivered for different reasons.

Here is a simple stored procedure which we can scheduled to re-run the failed subscriptions, this helps us from verifying of there are any failed subscriptions and running them manually.


/**************************************************************************/
CREATE PROCEDURE  [dbo].[FailedReportsSubscriptionsOnDemand]       

AS       

BEGIN       
       
SET NOCOUNT ON;       
       
DECLARE @ValueCount INT       
       
SELECT @ValueCount = COUNT(1)       
FROM   ReportSchedule rs       
JOIN   Schedule        ON   rs.ScheduleID = Schedule.ScheduleID       
JOIN   Subscriptions s ON  rs.SubscriptionID = s.SubscriptionID       
JOIN   [Catalog] c     ON  rs.ReportID = c.ItemID AND s.Report_OID = c.ItemID       
WHERE  (LastStatus LIKE 'Error%'  OR s.laststatus     LIKE '%fail%' OR s.LastStatus LIKE '%cannot%')     
AND     CONVERT(DATE, s.LastRunTime) >= CONVERT(DATE,GETDATE())       
       
--  SELECT @ValueCount       
       
-- If report failed is greater than 0 then run the command else print message       
IF @ValueCount > 0       
       
BEGIN       

DECLARE @sql VARCHAR(2000);       
SET @sql = '';       
SELECT @sql = @sql + 'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(s.SubscriptionID AS VARCHAR(500)) + ''';' + CHAR(13)       
FROM   ReportSchedule rs       
JOIN   Schedule        ON  rs.ScheduleID = Schedule.ScheduleID       
JOIN   Subscriptions s ON  rs.SubscriptionID = s.SubscriptionID       
JOIN   [Catalog] c     ON  rs.ReportID = c.ItemID AND s.Report_OID = c.ItemID       
WHERE  (LastStatus LIKE 'Error%'  OR s.laststatus     LIKE '%fail%' OR s.LastStatus LIKE '%cannot%' )     
AND    CONVERT(DATE, s.LastRunTime) >= CONVERT(DATE,GETDATE())       
--AND s.SubscriptionID='1B7178FD-BDCB-425D-969E-0F5EADF494F6'       
--PRINT @sql;       
EXEC (@sql);       

END       
--RETURN 0;       
       
END

/*******************************************************************************/