Automate 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

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