Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Resend Failed Scheduled Reports on Demand in SSRS


Resend Failed Scheduled Reports on Demand in SSRS

Author
Message
SqlServerLover
SqlServerLover
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 382
Comments posted to this topic are about the item Resend Failed Scheduled Reports on Demand in SSRS
yazalpizar_
yazalpizar_
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 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


thisisfutile
thisisfutile
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 973
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.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8694 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search