sqlserver查询最近失败的任务

select  job_id,step_name,message, 
    cast(
        (
            cast( LEFT ( run_date, 4 ) AS VARCHAR ) + '-' + SUBSTRING( cast( run_date AS VARCHAR ), 5, 2 ) + '-' + cast( RIGHT ( run_date, 2 ) AS VARCHAR ) 
            ) + ' ' + (
            cast( LEFT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR ) + ':' + SUBSTRING( cast( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ) AS VARCHAR ), 3, 2 ) + ':' + 
            cast( RIGHT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR ) 
        ) AS datetime )  datetime
 FROM msdb.dbo.sysjobhistory where  message like'%该作业失败%' and
    cast(
        (
            cast( LEFT ( run_date, 4 ) AS VARCHAR ) + '-' + SUBSTRING( cast( run_date AS VARCHAR ), 5, 2 ) + '-' + cast( RIGHT ( run_date, 2 ) AS VARCHAR ) 
            ) + ' ' + (
            cast( LEFT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR ) + ':' + SUBSTRING( cast( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ) AS VARCHAR ), 3, 2 ) + ':' + 
            cast( RIGHT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR ) 
        ) AS datetime )>=dateadd(minute,-1440,GETDATE())      
         



SELECT job_id，name from [msdb].[dbo].[sysjobs]