Resend Scheduled Reports(Failed) on Demand in SSRS

-- =============================================

USE [ReportServer]
GO

--==============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    Rahul Vairagi
-- Email : rahul.vairagi068@vairagi.com
-- Create date: 1/12/2011
-- Description:    This stored Procedure grabs the failed reports subscriptions ID and executes the subscriptions on demand.

-- =============================================
/* Testing

USE [ReportServer]
GO

EXEC [dbo].[USP_FailedReportsSubscriptionsOnDemand]

*/

-- =============================================

Alter PROCEDURE USP_FailedReportsSubscriptionsOnDemand


AS
BEGIN

SET NOCOUNT ON;

-- First insert the failed subscription ids into temporary table
select    Subscriptions.SubscriptionID
into #failedId
FROM ReportSchedule
        INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
        INNER JOIN Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
        INNER JOIN [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID
WHERE        Subscriptions.DeliveryExtension = 'Report Server Email'
 and Subscriptions.laststatus like '%fail%'
--Set the count of value into a variable
Declare @ValueCount int
select @ValueCount =count(SubscriptionID) from #failedId
-- If report failed is greater than 0 then run the command else print message

if @ValueCount > 0
Begin
DECLARE @sql varchar(1000);

set @sql = '';

select @sql = @sql + 'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''
 + cast(Subscriptions.SubscriptionID as varchar(500)) + ''';' + CHAR(13)
FROM ReportSchedule
 INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
 INNER JOIN Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
 INNER JOIN [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID
 AND Subscriptions.Report_OID = [Catalog].ItemID
 WHERE Subscriptions.DeliveryExtension = 'Report Server Email'
 and Subscriptions.laststatus like '%fail%' ;
EXEC(@sql);

end

else
PRINT 'No Reports were Failed'

END

GO