[SQL Server] Afficher l'historique d'exécution des jobs et des packages SSIS

  • Imprimer

-- =============================================

-- Author:          Zouhaier KHARROUBI

-- Create date:     18/02/2019

-- Description:     Afficher l'historique d'exécution des jobs et des packages SSIS

-- =============================================

        --Add the parameters for the stored procedure here

DECLARE      @Date_Debut                      AS DATETIME = NULL

              ,@Lancer_Requete                  AS BIT=1

              ,@destinataire_mail               AS VARCHAR(500)=NULL

 

BEGIN

 

       SET NOCOUNT ON;

       SET FMTONLY OFF;

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_LiesTemp]'))

             BEGIN

                    DROP TABLE #Serveurs_LiesTemp

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_Lies]'))

             BEGIN

                    DROP TABLE #Serveurs_Lies

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Objets]'))

             BEGIN

                    DROP TABLE #Objets

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ResultatTemp]'))

             BEGIN

                    DROP TABLE #ResultatTemp

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))

             BEGIN

                    DROP TABLE #Resultat

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ResultatFinal]'))

             BEGIN

                    DROP TABLE #ResultatFinal

             END

 

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeJob]'))

             BEGIN

                    DROP TABLE   #ListeJob

             END

 

       CREATE TABLE #Serveurs_LiesTemp

       (

       SRV_NAME                          VARCHAR(150),

       SRV_PROVIDERNAME           VARCHAR(150),

       SRV_PRODUCT                       VARCHAR(150),

       SRV_DATASOURCE                    VARCHAR(150),

       SRV_PROVIDERSTRING         VARCHAR(150),

       SRV_LOCATION               VARCHAR(150),

       SRV_CAT                                 VARCHAR(150)

       )                         

 

       CREATE TABLE #Serveurs_Lies

       (

       Num_Lig                                 INT IDENTITY (1,1),

       SRV_NAME                          VARCHAR(150),

       SRV_DATASOURCE                    VARCHAR(150),

       ServerName                        VARCHAR(150) NULL

       ) 

 

       CREATE NONCLUSTERED INDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies

       (

             ServerName ASC

       )

 

 

       CREATE TABLE #Objets

       (

       Valeur                            VARCHAR(150)  NULL,

       Type_Val                          VARCHAR(70)         NULL

       )

 

       CREATE NONCLUSTERED INDEX [IDX_#Objets_Type_Val] ON #Objets

       (

             Type_Val ASC

       )

 

       CREATE TABLE #Resultat

       (

             Num_Lig                                                      INT                                     ,

             Environnement                                  varchar(100)        NULL,

             Serveur_Lie                                           varchar(150)        NULL,

             Nom_Serveur                                           varchar(150)        NULL,

             Job_Name                                              sysname                           NULL,

             instance_id_job                                       bigint                     NULL,

             run_date_job                                   int                               NULL,

             run_time_job                                   varchar(8000)       NULL,

             date_heure_deb_exec_job                        datetime                   NULL,

             date_heure_fin_exec_job                        datetime                   NULL,

             run_duration_job                               varchar(8)                 NULL,

             run_status_job                                        int                               NULL,

             run_status_desc_job                            varchar(9)                 NULL,

             message_erreur_job                             nvarchar(4000)             NULL,              

             step_name                                             sysname                           NULL,

             command                                                      nvarchar(max)       NULL,

             instance_id_step_job                     bigint                     NULL,              

             run_date_step_job                              int                               NULL,

             run_time_step_job                              varchar(8000)       NULL,

             date_heure_deb_exec_step_job             datetime                   NULL,

             date_heure_fin_exec_step_job             datetime                   NULL,

             run_duration_step_job                          varchar(8)                 NULL,

             run_status_step_job                            int                               NULL,

             run_status_desc_step_job                 varchar(9)                 NULL,

             message_erreur_step_job                        nvarchar(4000)             NULL,

             Chemin_Package                                        nvarchar(526)       NULL,

             Chemin_Projet                                  nvarchar(543)       NULL,

             execution_id                                   bigint                     NULL,

             event_message_id                               bigint                     NULL,

             status_package                                        int                               NULL,

             status_desc_package                            varchar(18)                NULL,

             start_time_package                             datetime                   NULL,

             end_time_package                               datetime                   NULL,

             machine_name                                   nvarchar(128)       NULL,

             event_name                                            nvarchar(1024)             NULL,

             message_time                                   datetime                   NULL,

             message_erreur_package                         nvarchar(max)       NULL,

             message_source_name                            nvarchar(4000)             NULL,

             subcomponent_name                              nvarchar(4000)             NULL,

             execution_path                                        nvarchar(max)       NULL,

             CommandBackup                                  varchar(MAX)        NULL,

             destinataire_mail                              varchar(500)        NULL,

             Date_Debut_Requete                             datetime                   NULL,

             Date_Fin_Requete                               datetime                   NULL,

             Duree_Requete_Second                     int                               NULL,

             echec_collect                                  bit                               NULL,

             message_erreur_synthese                        varchar(max)        NULL,

             message_erreur_exec                            varchar(max)        NULL

       )

 

       CREATE NONCLUSTERED INDEX [IDX_#Resultat_Environnement_Serveur_Lie_Job_Name] ON #Resultat

       (

             Environnement                                  ASC,

             Serveur_Lie                                           ASC,

             Job_Name                                              ASC

       )

 

             CREATE TABLE #ResultatTemp

       (

             Num_Lig                                                      INT IDENTITY  (1,1),

             Environnement                                  varchar(100)        NULL,

             Serveur_Lie                                           varchar(150)        NULL,

             Nom_Serveur                                           varchar(150)        NULL,

             Job_Name                                              sysname                           NULL,

             instance_id_job                                       bigint                     NULL,

             run_date_job                                   int                               NULL,

             run_time_job                                   varchar(8000)       NULL,

             date_heure_deb_exec_job                        datetime                   NULL,

             date_heure_fin_exec_job                        datetime                   NULL,

             run_duration_job                               varchar(8)                 NULL,

             run_status_job                                        int                               NULL,

             run_status_desc_job                            varchar(9)                 NULL,

             message_erreur_job                             nvarchar(4000)             NULL,              

             step_name                                             sysname                           NULL,

             command                                                      nvarchar(max)       NULL,

             instance_id_step_job                     bigint                     NULL,              

             run_date_step_job                              int                               NULL,

             run_time_step_job                              varchar(8000)       NULL,

             date_heure_deb_exec_step_job             datetime                   NULL,

             date_heure_fin_exec_step_job             datetime                   NULL,

             run_duration_step_job                          varchar(8)                 NULL,

             run_status_step_job                            int                               NULL,

             run_status_desc_step_job                 varchar(9)                 NULL,

             message_erreur_step_job                        nvarchar(4000)             NULL,

             Chemin_Package                                        nvarchar(526)       NULL,

             Chemin_Projet                                  nvarchar(543)       NULL,

             execution_id                                   bigint                     NULL,

             event_message_id                               bigint                     NULL,

             status_package                                        int                               NULL,

             status_desc_package                            varchar(18)                NULL,

             start_time_package                             datetime                   NULL,

             end_time_package                               datetime                   NULL,

             machine_name                                   nvarchar(128)       NULL,

             event_name                                            nvarchar(1024)             NULL,

             message_time                                   datetime                   NULL,

             message_erreur_package                         nvarchar(max)       NULL,

             message_source_name                            nvarchar(4000)             NULL,

             subcomponent_name                              nvarchar(4000)             NULL,

             execution_path                                        nvarchar(max)       NULL,

             CommandBackup                                  varchar(MAX)        NULL,

             destinataire_mail                              varchar(500)        NULL,

             Date_Debut_Requete                             datetime                   NULL,

             Date_Fin_Requete                               datetime                   NULL,

             Duree_Requete_Second                     int                               NULL,

             echec_collect                                  bit                               NULL,

             message_erreur_synthese                        varchar(max)        NULL,

             message_erreur_exec                            varchar(max)        NULL

       )

 

       CREATE NONCLUSTERED INDEX [IDX_#ResultatTemp_Environnement_Serveur_Lie_Job_Name] ON #ResultatTemp

       (

             Environnement                                  ASC,

             Serveur_Lie                                           ASC,

             Job_Name                                              ASC

       )

 

       CREATE TABLE #ListeJob

       (

             Num_Lig                                                      INT IDENTITY (1,1),

             Serveur_Lie                                           varchar(150)        NULL,

             Job_Name                                              sysname                           NULL,

             date_heure_deb_exec_job                        datetime                   NULL

       )

 

 

 

       DECLARE       @Num_Lig                         AS INT

                    ,@Nbre_Lig                        AS INT

                    ,@Serveur_Lie              AS VARCHAR(150)

                    ,@ServerName               AS NVARCHAR(4000)

                    ,@DataBaseName                    AS NVARCHAR(150)

                    ,@Requete                         AS NVARCHAR(MAX)

                    ,@Date_DebutCast           AS NVARCHAR(30)

                    ,@LocalHot                        AS VARCHAR(50)

                    ,@Date_Debut_Requete AS DATETIME

                    ,@Date_Fin_Requete         AS DATETIME

                    ,@Date_Courante                   AS DATE

      

 

    -- Insert statements for procedure here

 

       SELECT @LocalHot           = @@SERVERNAME

               ,@Date_Courante   = GETDATE()

               --,@Date_Courante = '20190218'

 

       IF  @Date_Debut IS NULL

             BEGIN

                    SELECT @Date_Debut=DATEADD(HOUR,9,CAST(DATEADD(DAY,-1,@Date_Courante) AS DATETIME))

             END

 

       SELECT @Date_DebutCast= CONVERT(NVARCHAR(30),@Date_Debut, 126)

 

      

       IF @Lancer_Requete=1

             BEGIN

                    INSERT INTO #Serveurs_LiesTemp

                    EXEC sp_linkedservers 

 

 

                     INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)

                    SELECT DISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp WHERE SRV_NAME NOT IN('MonServeur_1','MonServeur_2')

                    --AND SRV_NAME='MonServeur_3'

            

                    SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies

 

                    WHILE  @Num_Lig <= @Nbre_Lig

                           BEGIN

                                  BEGIN TRY

 

                                        SELECT @Serveur_Lie =SRV_DATASOURCE,@ServerName = NULL FROM #Serveurs_Lies WHERE Num_Lig=@Num_Lig

 

                                       

                                        SELECT @Requete     = N'SELECT @ServerName_Out=ServerName FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + N',''SELECT @@SERVERNAME AS ServerName'')'

                                        EXECUTE sp_executesql @Requete,N'@ServerName_Out VARCHAR(150) OUTPUT',@SERVERNAME_Out=@ServerName OUTPUT

 

                                        IF NOT EXISTS (SELECT ServerName FROM #Serveurs_Lies WHERE ISNULL(ServerName,'')=@ServerName)

                                               BEGIN

 

                                                      UPDATE #Serveurs_Lies

                                                             SET

                                                             ServerName=@ServerName

                                                      WHERE Num_Lig=@Num_Lig

 

                                                      TRUNCATE TABLE #Objets

 

                                                      --Vérifier l'existance de la base SSISDB

                                                      SELECT @Requete     = N'SELECT name AS Valeur, ''DATABASE'' AS Type_Val FROM ' + QUOTENAME(@Serveur_Lie) + '.master.sys.databases WHERE name=''SSISDB'''

                   

                                                      INSERT INTO #Objets (Valeur,Type_Val)

                                                      EXECUTE sp_executesql @Requete

 

                                                      SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END

 

                                                      IF EXISTS (SELECT Valeur FROM #Objets WHERE Type_Val='DATABASE')

                                                             BEGIN

                                                                                                                                      

                                                                   SELECT @Requete = @Requete + N'SELECT DISTINCT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie, ''''' + @ServerName + ''''' AS Nom_Serveur,T1.[name] AS Job_Name,T3.instance_id AS instance_id_job,T3.run_date AS run_date_job

                                                                   ,REPLICATE(''''0'''', 6 - LEN(T3.run_time))

                                                                   + CAST(T3.run_time AS CHAR(6)) AS run_time_job

                                 

                                                                   ,CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_job

                                 

                                                                   ,REPLICATE(''''0'''',6-LEN(CAST(T3.run_duration AS VARCHAR(8)))) + CAST(T3.run_duration AS VARCHAR(8))  AS run_duration_job,T3.run_status AS run_status_job

                                                                   ,run_status_desc_job = CASE T3.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T3.[message] AS message_erreur_job

                                                                    ,T2.step_name,T2.command,T4.instance_id AS instance_id_step_job,T4.run_date AS run_date_step_job

                                                                   ,REPLICATE(''''0'''', 6 - LEN(T4.run_time))

                                                                   + CAST(T4.run_time AS CHAR(6)) AS run_time_step_job

                                 

                                                                   ,CASE T4.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_step_job

                                                                  

                                                                   ,REPLICATE(''''0'''',6-LEN(CAST(T4.run_duration AS VARCHAR(8)))) + CAST(T4.run_duration AS VARCHAR(8)) AS run_duration_step_job,T4.run_status AS run_status_step_job

                                                                    ,run_status_desc_step_job = CASE T4.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T4.[message] AS message_erreur_step_job

 

                                                             ,T5.Chemin_Package,T5.Chemin_Projet,T5.execution_id,T5.event_message_id,T5.status_package,T5.status_desc_package

                                                                    ,T5.start_time_package,T5.end_time_package,T5.machine_name

                                                                   ,T5.event_name,T5.message_time

                                                                  ,T5.[message_erreur_package],T5.[message_source_name],T5.[subcomponent_name],T5.[execution_path]

                                                                   ,T6.CommandBackup'

 

                                                                   SELECT @Requete =  @Requete + N' FROM msdb.dbo.sysjobs T1

                                                                   JOIN msdb.dbo.[sysjobsteps] T2 ON T2.job_id=T1.job_id

                                                                   JOIN msdb.dbo.sysjobhistory T3 ON T3.job_id=T1.job_id AND T3.step_id=0'

 

                                                                   + N' AND CONVERT(datetime,

                                                                   STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                    STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                   + ' JOIN msdb.dbo.sysjobhistory T4 ON T4.job_id=T1.job_id AND T4.step_id=T2.step_id'

                                                                   + N' AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                   + N' AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>='

 

                                                                   + N'CONVERT(datetime,

                                                                   STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                    STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)'

 

                                                                   + N' LEFT JOIN (

                                                                   SELECT DISTINCT S5.event_message_id,S1.project_id,''''\SSISDB\''''+ S3.name +''''\Projets\'''' + S1.[name] + ''''\Packages\'''' + S2.[name] AS Chemin_Projet

                                                                   ,''''\SSISDB\''''+ S3.name +''''\'''' + S1.[name] + ''''\'''' + S2.[name] AS Chemin_Package,

                                                                   S4.[execution_id],

                                                                   S4.[status] AS status_package,

                                                                   [status_desc_package]= CASE S4.[status] WHEN 1 THEN ''''Created''''

                                                                                                                         WHEN 2 THEN ''''Running''''

                                                                                                                         WHEN 3 THEN ''''Canceled''''

                                                                                                                         WHEN 4 THEN ''''Failed''''

                                                                                                                         WHEN 5 THEN ''''Pending''''

                                                                                                                         WHEN 6 THEN ''''Ended Unexpectedly''''

                                                                                                                         WHEN 7 THEN ''''Succeeded''''

                                                                                                                         WHEN 8 THEN ''''Stopping''''

                                                                                                                         WHEN 9 THEN ''''Completed ''''

 

                                                                   ELSE ''''Inconnu''''

                                                                   END'

 

                                                                   SELECT @Requete =  @Requete + N',CONVERT (datetime, S4.[start_time]) AS start_time_package,

                                                                   CONVERT (datetime, S4.[end_time]) AS end_time_package,

                                                                    /* CONVERT(FLOAT,DATEDIFF(millisecond, S4.[start_time], ISNULL(S4.[end_time], SYSDATETIMEOFFSET())))/1000 AS duration_package, */

                                                                   S4.[machine_name],

                                                                    S5.event_name,CONVERT(DATETIME,S5.message_time) AS message_time,

                                                                   CASE WHEN LEN(S5.[message]) <= 4096 THEN S5.[message] ELSE LEFT(S5.[message], 1024) + ''''...'''' END AS [message_erreur_package],

                                                                   CASE WHEN LEN(S5.[message_source_name]) <= 1024 THEN S5.[message_source_name] ELSE LEFT(S5.[message_source_name], 1024) + ''''...'''' END AS [message_source_name],

                                                                   CASE WHEN LEN(S5.[subcomponent_name]) <= 1024 THEN S5.[subcomponent_name] ELSE LEFT(S5.[subcomponent_name], 1024) + ''''...'''' END AS [subcomponent_name]

                                                                   ,CASE WHEN LEN(S5.[execution_path]) <= 1024 THEN S5.[execution_path] ELSE LEFT(S5.[execution_path], 1024) + ''''...'''' END AS [execution_path]                               

                                                                   FROM [SSISDB].[internal].[projects] S1

                                                                   JOIN [SSISDB].[internal].[packages] S2 ON S2.project_id=S1.project_id

                                                                   JOIN [SSISDB].[internal].[folders] S3 ON S3.folder_id=S1.folder_id'

 

                                                                   SELECT @Requete =  @Requete + N' JOIN [SSISDB].[catalog].[executions] S4 ON S4.folder_name=S3.name AND S4.project_name=S1.name AND S4.package_name=S2.name

                                                                   JOIN [SSISDB].[catalog].[event_messages] S5 ON S5.operation_id=S4.execution_id

                                                                   LEFT JOIN [SSISDB].[catalog].[extended_operation_info] S6 ON S6.info_id=S5.extended_info_id

                                                                   WHERE S4.[start_time] >=''''' + @Date_DebutCast + ''''''

                                                                   + N') T5 ON T2.command LIKE ''''%'''' + Chemin_Package + ''''%'''' COLLATE French_CI_AS

                                                                   AND T5.start_time_package >= CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END

                                                                   AND T5.end_time_package <= DATEADD(SECOND,T3.run_duration,CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END)'

                                                                   + N' CROSS APPLY ('

                                                                   + N'SELECT TOP 1 Command AS CommandBackup

                                                                   FROM [msdb].[dbo].[sysjobsteps]

                                                                   WHERE command LIKE ''''%%_BACKUP%'''''

                                                             + N' ) T6'                             

                                                             + ' WHERE T1.name NOT LIKE ''''%cyclique%'''' AND T4.run_status<>1 AND T3.run_status<>1 /*AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + '''''*/'

                                                                    

                                                             END

                                                      ELSE

                                                             BEGIN

                                                                   IF NOT EXISTS (SELECT Valeur FROM #Objets WHERE Type_Val='DATABASE')

                                                                          BEGIN

                                                                                

                                                                                 SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END

 

                                                                                 SELECT @Requete = @Requete     + N'SELECT DISTINCT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie,''''' + @ServerName + ''''' AS Nom_Serveur,T1.[name] AS Job_Name,T3.instance_id AS instance_id_job,T3.run_date AS run_date_job

                                                                                 ,REPLICATE(''''0'''', 6 - LEN(T3.run_time))

                                                                                 + CAST(T3.run_time AS CHAR(6)) AS run_time_job

                                 

                                                                                 ,CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                                 CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_job

                                                                  

                                                                                 ,REPLICATE(''''0'''',6-LEN(CAST(T3.run_duration AS VARCHAR(8)))) + CAST(T3.run_duration  AS VARCHAR(8)) AS run_duration_job,T3.run_status AS run_status_job

                                                                                 ,run_status_desc_job = CASE T3.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T3.[message] AS message_erreur_job

                                                                                 ,T2.step_name,T2.command,T4.instance_id AS instance_id_step_job,T4.run_date AS run_date_step_job

                                                                                 ,REPLICATE(''''0'''', 6 - LEN(T4.run_time))

                                                                                 + CAST(T4.run_time AS CHAR(6)) AS run_time_step_job

                                 

                                                                                 ,CASE T4.run_date WHEN 0 THEN NULL ELSE

                                                                                 CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_step_job

                                                                  

                                                                                 ,REPLICATE(''''0'''',6-LEN(CAST(T4.run_duration AS VARCHAR(8)))) + CAST(T4.run_duration  AS VARCHAR(8)) AS run_duration_step_job,T4.run_status AS run_status_step_job

                                                                                 ,run_status_desc_step_job = CASE T4.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T4.[message] AS message_erreur_step_job

                                                                                 ,NULL AS Chemin_Package,NULL AS Chemin_Projet,NULL AS execution_id,NULL AS event_message_id,NULL AS status_package,NULL AS status_desc_package

                                                                                 ,NULL AS start_time_package,NULL AS end_time_package,NULL AS machine_name

                                                                                 ,NULL AS event_name,NULL AS message_time

                                                                                 ,NULL AS [message_erreur_package],NULL AS [message_source_name],NULL AS [subcomponent_name],NULL AS [execution_path]

                                                                                 ,T5.CommandBackup'

                                                                                 SELECT @Requete = @Requete + N' FROM msdb.dbo.sysjobs T1

                                                                                 JOIN msdb.dbo.[sysjobsteps] T2 ON T2.job_id=T1.job_id

                                                                                 JOIN msdb.dbo.sysjobhistory T3 ON T3.job_id=T1.job_id AND T3.step_id=0'

 

                                                                                 + ' AND CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                                 + N' JOIN msdb.dbo.sysjobhistory T4 ON T4.job_id=T1.job_id AND T4.step_id=T2.step_id'

                                                                                 + ' AND CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                                 + N' AND CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>='

 

                                                                                 + N'CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)'

 

                                                                                 + N' CROSS APPLY ('

                                                                                 + N'SELECT TOP 1 Command AS CommandBackup

                                                                                 FROM [msdb].[dbo].[sysjobsteps]

                                                                                 WHERE command LIKE ''''%LOG_BACKUP%'''' OR command LIKE ''''%%_BACKUP%'''''

                                                                                 + N' ) T5'                      

                                                                                 + ' WHERE T1.name NOT LIKE ''''%cyclique%'''' AND T4.run_status<>1 AND T3.run_status<>1 AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

                                                                          END

                                                             END

                                                            

                                                             SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END

                                                                                                                                                                                            

                                                             SELECT @Date_Debut_Requete = GETDATE()

 

 

                                                             INSERT INTO #ResultatTemp (Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job

                                                             ,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job  

                                                      ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job

                                                      ,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

                                                      ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup)

                                                             EXECUTE sp_executesql @Requete

 

                                                             SELECT @Date_Fin_Requete = GETDATE()

 

                                                             UPDATE #ResultatTemp

                                                             SET

                                                                    Date_Debut_Requete   = @Date_Debut_Requete

                                                                   ,Date_Fin_Requete     = @Date_Fin_Requete

                                                                   ,Duree_Requete_Second = DATEDIFF(second,@Date_Debut_Requete,@Date_Fin_Requete)

                                                                   ,date_heure_fin_exec_job = DATEADD(SECOND,(3600 * SUBSTRING(run_duration_job,1,2)) + (SUBSTRING(run_duration_job,3,2) * 60) + SUBSTRING(run_duration_job,5,2),date_heure_deb_exec_job)

                                                                   ,date_heure_fin_exec_step_job = DATEADD(SECOND,(3600 * SUBSTRING(run_duration_step_job,1,2)) + (SUBSTRING(run_duration_step_job,3,2) * 60) + SUBSTRING(run_duration_step_job,5,2),date_heure_deb_exec_step_job)

                                                             WHERE Serveur_Lie=@Serveur_Lie

 

                                               END

 

                                  END TRY

                                  BEGIN CATCH

                                        INSERT INTO #ResultatTemp (Serveur_Lie,Job_Name,date_heure_deb_exec_job,message_erreur_exec,echec_collect) VALUES (@Serveur_Lie,'Ma Requete',GETDATE(), ERROR_MESSAGE(),1)

                                  END CATCH

            

                                  SELECT  @Num_Lig =   @Num_Lig + 1

 

                           END

 

                    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[HistoJob]'))

                           BEGIN

                                  DROP TABLE [tempdb]..[HistoJob]

                           END

 

                    INSERT INTO #ListeJob (Serveur_Lie,Job_Name,date_heure_deb_exec_job)

                    SELECT Serveur_Lie,Job_Name,MAX(date_heure_deb_exec_job) AS date_heure_deb_exec_job  FROM #ResultatTemp

                    GROUP BY Serveur_Lie,Job_Name

 

                    SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Serveur_Lie) FROM #ListeJob

 

                    WHILE @Num_Lig <= @Nbre_Lig

                           BEGIN

 

                                  INSERT INTO #Resultat (Num_Lig,Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job,date_heure_fin_exec_job

                                               ,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job  

                                        ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job

                                        ,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

                                        ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup,Date_Debut_Requete

                                               ,Date_Fin_Requete,Duree_Requete_Second,echec_collect,message_erreur_exec)                    

                                 

                                  SELECT TOP 10 T1.Num_Lig,T1.Serveur_Lie,Nom_Serveur,T1.Job_Name,instance_id_job,run_date_job,run_time_job,T1.date_heure_deb_exec_job,date_heure_fin_exec_job

                                               ,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job  

                                        ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job

                                        ,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

                                        ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup,Date_Debut_Requete

                                               ,Date_Fin_Requete,Duree_Requete_Second,echec_collect,message_erreur_exec

                                  FROM #ResultatTemp T1

                                  JOIN #ListeJob T2 ON T2.Serveur_Lie=T1.Serveur_Lie AND T2.Job_Name=T1.Job_Name AND T2.date_heure_deb_exec_job=T1.date_heure_deb_exec_job AND T2.Num_Lig=@Num_Lig

                                  WHERE (ISNULL(T1.event_name,'OnError')='OnError' OR ISNULL(T1.event_name,'OnWarning')='OnWarning')

 

 

                                  SELECT @Num_Lig =  @Num_Lig + 1

                           END

 

 

                    UPDATE #Resultat

                    SET

                           Environnement= CASE WHEN CommandBackup  LIKE '%SAUVEGARDE_PRODUCTION%' THEN 'PRODUCTION'

                                                      WHEN CommandBackup         LIKE '%SAUVEGARDE_HOMOLOGATION%' THEN 'HOMOLOGATION'

                                                      WHEN CommandBackup         LIKE '%SAUVEGARDE_DEVELOPPEMENT%' THEN 'DEVELOPPEMENT'

                                                      ELSE 'INCONNU'

                                                      END

                           ,destinataire_mail = CASE  WHEN Job_Name LIKE '%application_1%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'

                                                                          WHEN Job_Name LIKE '%application_2%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'

                                                                          WHEN Job_Name LIKE '%application_3%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'

                                                                          ELSE 'Destinataire Mail Inconnu'

                                                                   END

                    WHERE Environnement IS NULL

 

                    UPDATE #Resultat

                    SET

                           Environnement= CASE WHEN Serveur_Lie LIKE '%PRD%' THEN 'PRODUCTION'

                                                             WHEN Serveur_Lie LIKE '%HOM%' THEN 'HOMOLOGATION'

                                                             WHEN Serveur_Lie