Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
boing
Starting Member
8 Posts |
 Posted - 2006-09-02 : 13:58:55
|
Hi,I'm sure this is pretty simple, but I'm stumped as how to do it myself. Basically what I want to do is get some information on SQL jobs, using tables in MSDB. Here's the non-working statement:select name, count(*) from sysjobs sj where sj.job_id in (select job_id from sysjobhistory where run_status = '0') This obviously won't work. What I want to do is get the name from sysjobs, and then beside it have the amount of times that job failed (run_status = 0 means that job failed), if the job failed. If not I want 0, or NULL...In sysjobshistory, theres a row for each time a job was run, and it sets run_status accordingly.The job_id in sysjobs matches the job_id in sysjobshistory.How can I construct this statement? The problem is that I want all the job names, and a count for that job on the amount of times it failed, if it failed.It's easy to do in two seperate selects, but I'm using a stored procedure, so it must all be in one recordset. My SQL (obviously) isn't great, but I suspect a solution is easy enough???Any help would be HUGELY appreciated.Thanks,Cormac Redmond |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-09-02 : 15:05:53
|
| Select j.[name] , IsNull(h.FailedJobCount, 0) As FailedJobCountFrom sysjobs jLeft Outer Join (Select job_id, Count(*) As FailedJobCount From sysjobhistory Where run_status = '0' Group By job_id) h On j.job_id = h.job_id |
 |
|
|
boing
Starting Member
8 Posts |
Posted - 2006-09-02 : 19:14:36
|
| Thanks man. So obvious once you see it! |
|
|
|
|
|
|
|
|
Simple SQL Select - please help