June 16, 2010 at 7:14 am
Hello,
I'm wondering if this is even possible: I'm trying to run a query on the msdb database to get a bunch of information on SQL Agent jobs - in particular how long it took to run the last time it ran. What I've got is everything (lifted from another post) except for the last bit.
Here's my stripped down TSQL:
select [A BUNCH OF STUFF]
from msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
left join
(
select HISTORY.job_id, HISTORY.run_duration, HISTORY.run_date
from msdb.dbo.sysjobhistory HISTORY
where run_duration is not null
AND HISTORY.job_id = SJ.job_id
--order by run_date
and run_date > '20100610'
) as jobHistory on jobHistory.job_id = SJ.job_id
where S.enabled = 1
and run_duration > 500
order by run_duration
When I run this I get the following error:
The multi-part identifier "SJ.job_id" could not be bound.
This type of syntax seems to work when used as a subquery in result members or just as a normal correlated subquery - but when used within the join it doesn't seem to play nice. Is it my syntax or is it just that I can't do this?
Thanks.
June 16, 2010 at 7:20 am
You don't need to put the job_id equality condition inside the correlated subquery, since there's the ON clause to do that:
select *
from msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
left join ( select HISTORY.job_id,
HISTORY.run_duration,
HISTORY.run_date
from msdb.dbo.sysjobhistory HISTORY
where run_duration is not null
--AND HISTORY.job_id = SJ.job_id --> You don't need this line
and run_date > '20100610'
) as jobHistory on jobHistory.job_id = SJ.job_id
where S.enabled = 1
and run_duration > 500
order by run_duration
-- Gianluca Sartori
June 16, 2010 at 7:32 am
Thanks... BUT - I forgot to include one important piece of information: I want the inner query to just grab the max run time... but let me give this a shot. Thanks again.
June 16, 2010 at 8:11 am
This makes things a little bit more complicated, but not impossible:
SELECT *
FROM msdb.dbo.sysjobschedules SJ
JOIN msdb.dbo.sysjobs S ON S.job_id = SJ.job_id
JOIN msdb.dbo.sysschedules SS ON ss.schedule_id = sj.schedule_id
LEFT JOIN ( SELECT job_id,
MAX(run_date) AS run_date,
run_duration
FROM msdb.dbo.sysjobhistory HISTORY
WHERE EXISTS ( SELECT job_id,
MAX(run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory H1
WHERE run_duration IS NOT NULL
AND run_date > '20100610'
GROUP BY job_id
HAVING job_id = HISTORY.job_id
AND MAX(run_duration) = HISTORY.run_duration )
GROUP BY job_id,
run_duration
) AS jobHistory ON jobHistory.job_id = SJ.job_id
WHERE S.enabled = 1
AND run_duration > 500
ORDER BY run_duration
It would be much simpler in SQL2005 with CROSS APPLY syntax, but this is not the case.:crying:
-- Gianluca Sartori
June 17, 2010 at 9:15 am
Thanks so much! I haven't tried this yet but this should be very helpful.
June 17, 2010 at 9:22 am
You're welcome.
Let me know if this worked for you.
-- Gianluca Sartori
June 17, 2010 at 9:36 am
Actually it's almost perfect except I put the max around the run_duration and grouped by job_id and run_date inside the LEFT JOIN.
from msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
LEFT JOIN ( SELECT job_id,
run_date AS run_date,
max(run_duration) as max_run_duration
FROM msdb.dbo.sysjobhistory HISTORY
WHERE EXISTS ( SELECT job_id,
MAX(run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory H1
WHERE run_duration IS NOT NULL
AND run_date > '20100610'
GROUP BY job_id
HAVING job_id = HISTORY.job_id
AND MAX(run_duration) = HISTORY.run_duration )
GROUP BY job_id,
run_date
) AS jobHistory ON jobHistory.job_id = SJ.job_id
where S.enabled = 1
and max_run_duration > 100
order by max_run_duration
Thanks again.
June 17, 2010 at 10:08 am
Great.
Glad I could help.
-- Gianluca Sartori
June 17, 2010 at 6:45 pm
This must be more clean:
SELECT *
FROM msdb.dbo.sysjobhistory HISTORY
INNER JOIN (SELECT job_id, MAX(run_duration) AS max_run_duration
FROM msdb.dbo.sysjobhistory
WHERE run_duration IS NOT NULL
AND run_date > '20100610'
GROUP BY job_id ) H1 ON H1.max_run_duration = HISTORY.run_duration
RIGHT JOIN msdb.dbo.sysjobs S on HISTORY.job_id = S.job_id
INNER JOIN msdb.dbo.sysjobschedules SJ ON S.job_id = SJ.job_id
INNER JOIN msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
where S.enabled = 1
and H1.max_run_duration > 100
order by max_run_duration
And no correlated subqueries required.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply