Resend Failed Scheduled Reports on Demand in SSRS

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


USE [ReportServer]

GO


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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author:    Pratima Paudel

-- Email : pratima_paudel1985@yahoo.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