SQL Clone
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
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 418
Comments posted to this topic are about the item Resend Failed Scheduled Reports on Demand in SSRS
yazalpizar_
yazalpizar_
Say Hey Kid
Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)

Group: General Forum Members
Points: 698 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 1039
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
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38710 Visits: 886
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