Resend the reports scheduled to send on today's date

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

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 reports scheduled to run on today's date and reruns the subscriptions on demand.

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

USE [ReportServer]
GO

--EXEC [dbo].[USP_FailedReportsSubscriptionsOnDemand_ReplicationFailed]

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

Alter PROCEDURE USP_FailedReportsSubscriptionsOnDemand_ReplicationFailed


AS
BEGIN

SET NOCOUNT ON;


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 convert(varchar(20),Subscriptions.lastruntime, 101) = convert(varchar(20),getdate(), 101);
 
EXEC(@sql);

END

GO