[SQL Server] Afficher l'historique d'exécution des jobs et des packages SSIS
-- =============================================
-- 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=