Resend Failed Scheduled Reports on Demand in SSRS

• SqlServerLover

  Ten Centuries

  Points: 1348

  More actions

  January 29, 2011 at 2:10 pm

  #388733

  Comments posted to this topic are about the item Resend Failed Scheduled Reports on Demand in SSRS

• yazalpizar_

  Default port

  Points: 1425

  More actions

  November 13, 2014 at 1:00 am

  #1758149

  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

• thisisfutile

  Hall of Fame

  Points: 3669

  More actions

  April 1, 2016 at 1:43 pm

  #1869259

  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.

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 17, 2016 at 7:05 am

  #1879524

  Thanks for the script.