Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Thursday, August 25, 2016 1:36 PM
Points: 72, Visits: 382
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: Tuesday, July 26, 2016 2:19 PM
Points: 148, Visits: 626
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
Posted Friday, April 1, 2016 1:43 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, November 15, 2016 3:03 PM
Points: 122, Visits: 964
This works great! Thanks for posting it!

The only thing I had to change was the @SQL variable datatype. I went with varchar(max) because we have a lot of subscriptions. Varchar(2000) is only good for about 17 failed subscriptions and the day I tested this we had about 100 that failed.
Post #1774338
Posted Tuesday, May 17, 2016 7:05 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 1, 2016 7:11 AM
Points: 8,298, Visits: 852
Thanks for the script.
Post #1786860
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse