Get all Packages that are failed to execute
-
Hi All, I have an application which reads all the Jobs and its related packages which failed, but its reading only those Packages which are executed as part of the Jobs. Is there any way that I can check all the Packages that are failed like the ones that are executed externally like from an Application or Manually etc. The code to get the failed packages that are part of the SQL Agent Jobs: DECLARE @PreviousDate datetime DECLARE @Year VARCHAR(4) DECLARE @Month VARCHAR(2) DECLARE @MonthPre VARCHAR(2) DECLARE @Day VARCHAR(2) DECLARE @DayPre VARCHAR(2) DECLARE @FinalDate INT -- Initialize Variables SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT) -- Final Logic SELECT h.instance_id, j.[name], s.step_name, h.step_id, h.step_name, h.run_date, h.run_time, h.sql_severity, h.message, h.server FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_id WHERE h.run_status = 0 -- Failure AND h.run_date > @FinalDate ORDER BY h.instance_id DESC
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."