Dynamic TSQL to rerun failed jobs

Question

Dynamic TSQL to rerun failed jobs

asdsfssd

Old Hand

Points: 384
More actions
January 29, 2013 at 10:52 am

#98697

Need help in scripting - I'm trying to create a script to run on SQL Servers with the following goals in mind.
1) It'll look for failed jobs.
2) Re-run those failed jobs.
The script I have:
-- Declare some variables
DECLARE @count int
DECLARE @rancount int
DECLARE @job varchar(50)
-- Get failed jobs to a temp table rerunjb
select a.name from msdb..sysjobs a, msdb..sysjobhistory b
into #rerunjb
where a.job_id = b.job_id
and b.run_status = 0
and CONVERT(VARCHAR(8), b.run_date, 112) = CONVERT(VARCHAR(8),getdate(),112)
order by b.name
set @rancount = 0
set @count = (select count(*) from #rerunjb)
if @count > 0
while (@rancount < @count)
BEGIN
set @job = (select name from #rerunjb)
--comment out for now
--exec msdb..sp_start_job @job_name = '@job'
PRINT @job
set @rancount = @rancount+1
end
I'm getting 2 row(s) affected (which is right - 2 rows are inserted into #rerunjb);
but I only get Job1 (from the PRINT @job). For some reason it's not looping.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Do you get job1 printed twice? or only once?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

asdsfssd Old Hand Points: 384 More actions · Answer 2

I'm getting twice Job 1. And I think the issue is

set @job = (select TOP 1 name from #rerunjb)