Is This A Catch-22

[JayKusch]

Have an SP that will return the status of a job from the sysjobhistory table. ie.

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress

All status respond correctly EXCEPT "4 = In Progress". When I run the SP, when a job is POSITIVELY running, I do not get that job returned.

If I look in the sysjobhistory table, I never see that job, or any job for that matter, with a status of 4.

My question is: Since status 4 is "In Progress", is that why it cannot show in the sysjobHISTORY file because it is not HISTORY yet?

Is there a workaround to get what I am after? Here is the main snipette of code from my SP ...



SELECT Job_ID = j.Job_ID,
Job_Status = h.Run_Status,
Run_Date = h.Run_Date,
Run_Time = h.Run_Time,
Job_Name = convert(char(80),j.Name),
Step_Name = h.Step_Name,
Comments = 'Backups are IN PROGRESS mode ... Please verify status of job' + Replace(Convert(VarChar(770),h.Message),'Executed as user: sa. ',''),
Record_Type = 'UNKNOWN',
GetDate()
FROM MSDB.dbo.SysJobHistory AS h
INNER JOIN MSDB.dbo.SysJobs AS j ON (h.Job_ID = j.Job_ID)
INNER JOIN MSDB.dbo.SysCategories AS c ON (j.Category_ID = c.Category_ID)
WHERE h.Run_Status = 4 AND
h.Run_Date >= Convert(Char(8),GetDate(),112) AND
c.Name = 'Daily Backups' AND
h.Step_ID = 1




Thanks

J. Kusch

 

[oublie]

Logic would dictate that as long as the jobs exists then if it does not have a status of 0,1,2 or 3 then it must be in status 4. I would suggest modifying your code so that if a sysjobs exists and does not have one of the first 4 status's then it is shown as having a staus 4. There is not other status it could be.

hope this is what your looking for

WHERE h.Run_Status not in(0,1,2,3) AND ....

 

[JayKusch]

Close ... Problem is that there is NO record w/ any status for the job in SysJobHistory to check if the status is not in (0,1,2,3) when the job is in status 4. Thats the Catch-22.

Seems to only create the record in the sysjobhistory table when there is some type of termination of the initial job but does not create the record while it is "In Progress".

Your suggestion did give me a new option to use. I may be able to use a NOT EXISTS clause, in some form or fashion, to accomplish what I am after.

Thanks for the help!

Thanks

J. Kusch

 

[JohnEck]

I am not really familiar with the all the systems table but I bet the job is in the sysobjects table and if you do a left join of sysobjects to sysjobhistory selecting only jobs. You can then us isnull(status,4) to get the in progress jobs.