Feb 3, 2011

Resend Scheduled Reports

-- =============================================
USE [ReportServer]
GO
--==============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    Rahul Vairagi
-- Email : rahul.vairagi068@gmail.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

 

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.