quote:

---

Originally posted by NguyenL71

How can I pass in the database name and return the status of running job from last night. I try to query out the status of backup job and input para is dbname. What other table I need to JOINs with?. Any helps is greatly appreciate.


DECLARE @DatabaseName VARCHAR(65)
SET @DatabaseName = 'Devdb'

SELECT CASE a.run_status
WHEN 0 THEN 'F'
WHEN 1 THEN 'S'
WHEN 2 THEN 'R'
WHEN 3 THEN 'C'
WHEN 4 THEN 'IP'
END, b.name
FROM msdb..sysjobhistory AS a
INNER JOIN msdb..sysjobs AS b
ON a.job_id = b.job_id
AND dbName = @DatabaseName ???
WHERE a.run_status = CONVERT(INT, CONVERT(CHAR(8), DATEADD(dd, -1, GETDATE()), 112)) -- return yesterday
go

---