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:29
|
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 |
|
|
|
|
|
|
|
SELECT from 2 tables question - Please help