SQL 2000 - tsql 找出過去 24 小時內失敗的作業

我想找出過去 24 小時內哪些 sql 代理作業失敗…如何使用 TSQL for SQL 2000 找到它？

問候 Manjot

和….

SELECT j.[name]

, left(cast(jh.run_date as char(10)),4) 

+ '-' + substring(cast(jh.run_date as char(10)),5,2)

+ '-' + substring(cast(jh.run_date as char(10)),7,2) 

+ ' ' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6), 1, 2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), jh.run_time), 6) ,3 ,2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6) ,5 ,2) as [Start_Time]

FROM
   msdb.dbo.sysjobhistory jh

INNER JOIN msdb.dbo.sysjobs j

ON  j.job_id = jh.job_id

and jh.step_id = 0

and jh.run_status = 0

and left(cast(jh.run_date as char(10)),4) 

+ '-' + substring(cast(jh.run_date as char(10)),5,2)

+ '-' + substring(cast(jh.run_date as char(10)),7,2) 

+ ' ' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6), 1, 2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), jh.run_time), 6) ,3 ,2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6) ,5 ,2) >= 
       CONVERT(char(19), GETDATE()-2, 121) 

SELECT
   j.[name]
 , jh.run_date
 , jh.run_time
 , jh.sql_severity
 , jh.message
FROM
   msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
ON  j.job_id = jh.job_id
WHERE
   jh.run_status = 0 -- Failure
AND jh.run_date > DATEADD(DAY, -1, GETDATE())
ORDER BY
   jh.run_date DESC

引用自：https://dba.stackexchange.com/questions/4171