sys-show-failed-sql-agent-steps.sql

/* ==================================================================== <HEADER>
Source      : sys-show-failed-sql-agent-steps.sql
Description : Toon alle SQL agent job steps van de afgelopen 7 dagen die een
              status <> 'Succeeded' hebben
============================================================== <PROGRAM HISTORY>
Date       Vers Name         Changes (Incident/Change Number)
---------- ---- ------------ --------------------------------------------------
  1       Created
Mutaties: http://dwh.mchaaglanden.local/gitphp/?sort=age
======================================================================== <NOTES>

select format ( getdate(), 'yyyy-MM-dd' ) AS FormattedDate;
SELECT FORMAT(getdate(), N'yyyy-MM-dd hh:mm') AS FormattedDateTime;
[NT-DK-CCPRO-P].[CCPro].[dbo].
[nt-vm-dwh-p3].dwh_ezis.dbo.
[HIXR.mchbrv.nl].[HIX_PRODUCTIE].[dbo].

==================================================================== <SOURCE> */

set nocount on -- Stop de melding over aantal regels
set ansi_warnings on -- ISO foutmeldingen(NULL in aggregraat bv)
set ansi_nulls on -- ISO NULLL gedrag(field = null returns null, ook als field null is)

SELECT
--    [sJOB].[job_id] AS [JobID]
     [sJOB].[name] AS [JobName]
--    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[last_run_outcome]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 5 THEN 'Unknown'
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
      AS [LastRunDuration (HH:MM:SS)]
--    , [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]
    , [sJSTP].[last_run_date]
    , CASE [sJSTP].[last_run_date]
        WHEN 0 THEN NULL
        ELSE
            CAST(
                CAST([sJSTP].[last_run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
where [sJSTP].[last_run_outcome] <> '1'
 and [sJSTP].[last_run_date] <> 0
 and [sJSTP].[last_run_date] > format ( dateadd(dd, -7, getdate()), 'yyyyMMdd' )
-- and case when [sJSTP].[last_run_date] <> 0 then cast([sJSTP].[last_run_date] as datetime) else '19990101' end between dateadd(dd, -7, getdate()) and getdate()
ORDER BY [last_run_date] desc, [JobName], [StepNo]

/*
=====================================================================<KLADBLOK>

select distinct
 tabel,
 kolomnaam,
 length
from
(
select distinct
  t2.name as tabel,
  t3.rows,
  t1.name as kolomnaam,
  t1.length
from dbo.syscolumns t1
  join dbo.sysobjects t2
   on t2.id = t1.id
  left join dbo.sysindexes t3
   on t3.id = t1.id and t3.name = t2.name
where upper(t2.name) like '%%'        -- tabel
and upper(t1.name) like '%%'        -- kolom
) t0 group by tabel, kolomnaam, length
order by 1

===============================================================================
*/