January 29, 2011 at 2:10 pm
Comments posted to this topic are about the item Resend Failed Scheduled Reports on Demand in SSRS
November 13, 2014 at 1:00 am
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
April 1, 2016 at 1:43 pm
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.
May 17, 2016 at 7:05 am
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply