For some reason, if you need to re-run the entire report schedules which were supposed to go out today, this stored procedure will do it in one click.
For some reason, if you need to re-run the entire report schedules which were supposed to go out today, this stored procedure will do it in one click.
-- ============================================= 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