As most of the organizations have multiple reports being run on the SSRS reporting servers and it is definite that we create subscriptions to send reports on timely basis and some do fail to be delivered for different reasons.
Here is a simple stored procedure which we can scheduled to re-run the failed subscriptions, this helps us from verifying of there are any failed subscriptions and running them manually.
/**************************************************************************/
CREATE PROCEDURE [dbo].[FailedReportsSubscriptionsOnDemand]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ValueCount INT
SELECT @ValueCount = COUNT(1)
FROM ReportSchedule rs
JOIN Schedule ON rs.ScheduleID = Schedule.ScheduleID
JOIN Subscriptions s ON rs.SubscriptionID = s.SubscriptionID
JOIN [Catalog] c ON rs.ReportID = c.ItemID AND s.Report_OID = c.ItemID
WHERE (LastStatus LIKE 'Error%' OR s.laststatus LIKE '%fail%' OR s.LastStatus LIKE '%cannot%')
AND CONVERT(DATE, s.LastRunTime) >= CONVERT(DATE,GETDATE())
-- SELECT @ValueCount
-- 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(s.SubscriptionID AS VARCHAR(500)) + ''';' + CHAR(13)
FROM ReportSchedule rs
JOIN Schedule ON rs.ScheduleID = Schedule.ScheduleID
JOIN Subscriptions s ON rs.SubscriptionID = s.SubscriptionID
JOIN [Catalog] c ON rs.ReportID = c.ItemID AND s.Report_OID = c.ItemID
WHERE (LastStatus LIKE 'Error%' OR s.laststatus LIKE '%fail%' OR s.LastStatus LIKE '%cannot%' )
AND CONVERT(DATE, s.LastRunTime) >= CONVERT(DATE,GETDATE())
--AND s.SubscriptionID='1B7178FD-BDCB-425D-969E-0F5EADF494F6'
--PRINT @sql;
EXEC (@sql);
END
--RETURN 0;
END
/*******************************************************************************/
No comments:
Write commentsPlease do not enter spam links