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 @ 12:17 PM
Points: 3,358, Visits: 7,263
Do you get job1 printed twice? or only once?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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