Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Resend Failed Scheduled Reports on Demand in SSRS Expand / Collapse
Author
Message
Posted Saturday, January 29, 2011 2:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:03 PM
Points: 64, Visits: 312
Comments posted to this topic are about the item Resend Failed Scheduled Reports on Demand in SSRS
Post #1055829
Posted Thursday, November 13, 2014 1:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:21 AM
Points: 145, Visits: 510
Useful script, I will make use of it inmediately. Since you posted maybe surely you have improve it or even you are using some other approach. I made couple of small changes.

USE [ReportServer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('FailedReportsSubscriptionsOnDemand') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE [dbo].[FailedReportsSubscriptionsOnDemand] AS BEGIN SET NOCOUNT ON; END')
IF @@ERROR = 0 PRINT 'Procedure [FailedReportsSubscriptionsOnDemand] created'
END
GO
/**********************************************************************************************************************
* Description: This stored Procedure grabs the failed reports subscriptions ID and executes the subscriptions on demand.
* Date: 1/12/2011
* Author: pratima_paudel1985@yahoo.com
*
* testing:
* USE [ReportServer]
* GO
* EXEC [dbo].[FailedReportsSubscriptionsOnDemand]
***********************************************************************************************************************/
ALTER PROCEDURE [dbo].[FailedReportsSubscriptionsOnDemand]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ValueCount INT

SELECT @ValueCount = COUNT(1)
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%'

-- If report failed is greater than 0 then run the command else print message
IF @ValueCount > 0
BEGIN
DECLARE @sql VARCHAR(2000);
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%' ;

--PRINT @sql;
EXEC (@sql);
END
RETURN 0;
END
GO

IF @@ERROR = 0 PRINT 'Procedure [FailedReportsSubscriptionsOnDemand] created/modified'
GO

Post #1634055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse