Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic TSQL to rerun failed jobs Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:35 PM
Points: 6, Visits: 29
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.
Post #1413195
Posted Tuesday, January 29, 2013 11:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 3,666, Visits: 8,000
Do you get job1 printed twice? or only once?


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1413213
Posted Tuesday, January 29, 2013 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:35 PM
Points: 6, Visits: 29
I'm getting twice Job 1. And I think the issue is
set @job = (select TOP 1 name from #rerunjb)
Post #1413225
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse