August 24, 2009 at 11:48 am
I thought I would post this here and hopefully someone can point out where my problem is. This query I am running I want it to return the job name , step name , run date , run time, message .. it works fine but
The query below should return a different message and runtime for every step, but it’s returning the same info for every step any thoughts?
DECLARE @jobname VARCHAR(250)
SET @jobname = 'Weekend DB Maintenance - Sat 10pm'
SELECT --@@SERVERNAME,
j.name,
s.step_name,
h.run_date,
h.run_time,
h.message
from msdb..sysjobs j
join msdb..sysjobhistory h on j.job_id = h.job_id
join msdb..sysjobsteps s on j.job_id = s.job_id
where j.name = @jobname
AND h.run_status = 1 -- 1 = success
--AND h.step_name = '(Job outcome)'
AND h.run_date = ( SELECT MAX(run_date)
FROM msdb..sysjobhistory h
JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name = @jobname AND h.step_id = 0
)
AND h.run_time = ( SELECT MAX(run_time)
FROM msdb..sysjobhistory h
JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE h.run_date = ( SELECT MAX(run_date)
FROM msdb..sysjobhistory h
JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name = @jobname AND h.step_id = 0
)
and j.name = @jobname
)
ORDER BY s.step_id
August 24, 2009 at 11:56 am
I can't tell for sure, but your Where clause looks like you're trying to just get the most recent run of the job. Is that correct?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2009 at 12:04 pm
I think you will need to join sysjobsteps and sysjobshistory on the step_id as well as the job_id.
I modifed your query but there is probably a better query to get this information. Anyway,
DECLARE @jobname VARCHAR(250)
SET @jobname = 'Test Job Name'
SELECT --@@SERVERNAME,
j.name,
s.step_name,
h.run_date,
h.run_time,
h.message
from msdb..sysjobs j
join msdb..sysjobhistory h on j.job_id = h.job_id
join msdb..sysjobsteps s on j.job_id = s.job_id AND h.step_id = s.step_id
where j.name = @jobname
AND h.run_status = 1 -- 1 = success
--AND h.step_name = '(Job outcome)'
AND h.run_date = ( SELECT MAX(run_date)
FROM msdb..sysjobhistory h
JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name = @jobname AND h.step_id = 0
)
AND h.run_time >= ( SELECT MAX(run_time)
FROM msdb..sysjobhistory h
JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE h.run_date = ( SELECT MAX(run_date)
FROM msdb..sysjobhistory h
JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name = @jobname AND h.step_id = 0
)
and j.name = @jobname
AND h.step_id = 0
)
ORDER BY s.step_id
August 24, 2009 at 12:25 pm
thansk for the responses: this is what I ended up doing and it returned exactly what I was looking for.
SELECT --@@SERVERNAME,
CURRENT_JOB_RUN.name,
s.step_name,
s.step_id,
h.run_date,
h.run_time,
h.message
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY j.job_id ORDER BY h.run_date DESC, h.run_time DESC ) ROW_NUM,
j.job_id,
j.name,
h.run_date,
h.run_time
FROM msdb..sysjobs j
INNER JOIN msdb..sysjobhistory h ON j.job_id = h.job_id
AND h.step_id = 0
) CURRENT_JOB_RUN
INNER JOIN msdb..sysjobhistory h ON CURRENT_JOB_RUN.job_id = h.job_id
AND CURRENT_JOB_RUN.job_id = h.job_id
AND CURRENT_JOB_RUN.ROW_NUM = 1
AND h.run_date >= CURRENT_JOB_RUN.run_date
and h.run_time >= CURRENT_JOB_RUN.run_time
INNER JOIN msdb..sysjobsteps s ON CURRENT_JOB_RUN.job_id = s.job_id
AND h.step_id = s.step_id
WHERE CURRENT_JOB_RUN.name = 'Weekend DB Maintenance - Sat 10pm'
--WHERE CURRENT_JOB_RUN.NAME = 'Weekend DB Maintenance - Sat 10pm'
ORDER BY CURRENT_JOB_RUN.name,
s.step_id
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply