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
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)
set @job = (select name from #rerunjb)
--comment out for now
--exec msdb..sp_start_job @job_name = '@job'
set @rancount = @rancount+1
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.