SHARE
TWEET

Untitled

a guest Jan 12th, 2017 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     j.name JobName,
  3.     h.step_name StepName,
  4.     CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
  5.     STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') RunTime,
  6.     h.run_duration StepDuration,
  7.     case h.run_status
  8.        when 0 then 'failed'
  9.        when 1 then 'Succeded'
  10.        when 2 then 'Retry'
  11.        when 3 then 'Cancelled'
  12.        when 4 then 'In Progress'
  13.     end as ExecutionStatus,
  14.     h.message MessageGenerated
  15. FROM
  16.     sysjobhistory h
  17. INNER JOIN
  18.     sysjobs j ON j.job_id = h.job_id
  19. ORDER BY
  20.     j.name, h.run_date DESC
  21. GO
  22.    
  23. SELECT j.name JobName,h.step_name StepName,
  24. CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
  25. STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
  26. h.run_duration StepDuration,
  27. case h.run_status when 0 then 'failed'
  28. when 1 then 'Succeded'
  29. when 2 then 'Retry'
  30. when 3 then 'Cancelled'
  31. when 4 then 'In Progress'
  32. end as ExecutionStatus,
  33. h.message MessageGenerated
  34.  sysjobs j
  35.  cross apply
  36.  (
  37.  select top 1 b.columns you need
  38.  from sys.jobhistory b where b.job_id=j.job_id
  39.  order by RunDate desc
  40.  ) h
RAW Paste Data
Top