Dynamic TSQL to rerun failed jobs

• asdsfssd

  Old Hand

  Points: 354

  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.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 29, 2013 at 11:33 am

  #1582275

  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: 354

  More actions

  January 29, 2013 at 12:01 pm

  #1582286

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

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