Rerunning all subscriptions for a particular report that have failed.

Today I had a situation where a few of the subscriptions for a particular report have failed. I needed a script to run only these subscriptions manually.  Basically I am extending my previous blog post to achieve this by passing the result of my query into a cursor and executing the subscriptions using the sp_start_job stored procedure as follows:

Do not forget to deallocate the cursor.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

use ReportServer go DECLARE @ScheduledReportName varchar(200) DECLARE @JobID uniqueidentifier DECLARE @LastRunTime datetime Declare @JobStatus Varchar(100) -------------------------------------------------------- DECLARE @RunAllReport CURSOR SET @RunAllReport = CURSOR FAST_FORWARD FOR SELECT CAT.[Name] AS RptName , res.ScheduleID AS JobID , sub.LastRuntime , LastStatus FROM dbo.Catalog AS cat INNER JOIN dbo.Subscriptions AS sub ON CAT.ItemID = sub.Report_OID INNER JOIN dbo.ReportSchedule AS res ON CAT.ItemID = res.ReportID AND sub.SubscriptionID = res.SubscriptionID INNER JOIN msdb.dbo.sysjobs AS job ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name] INNER JOIN msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id INNER JOIN dbo.Users U ON U.UserID = sub.OwnerID where res.ReportID ='3AA8BDD0456' AND LastRunTime > GETDATE() - 1 AND LastStatus not like 'Mail sent%' AND LastStatus not like '%has been saved to%' AND LastStatus not like '%0 errors%' --AND LastStatus not like 'Pending%' OPEN @RunAllReport FETCH NEXT FROM @RunAllReport INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus WHILE @@FETCH_STATUS = 0 BEGIN Print @ScheduledReportName --&' ' & @JobID EXEC msdb.dbo.sp_start_job @job_name =@JobID FETCH NEXT FROM @RunAllReport INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus END CLOSE @RunAllReport DEALLOCATE @RunAllReport

API • Code • Made by Alexander Kojevnikov • Powered by Flask and Pygme