[SQL Server] SQLCmd exécuter un fichier de commandes SQL

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

-- Author:          KHARROUBI Zouhaier

-- Create date:     03/03/2019

-- Description:     Exécuter un fichier de commandes SQL

/*Exemple d'utilisation

EXEC [dbo].[spr_Admin_ExecuteFileSQL]   @Instance_SQLServer='MonInstanceSQL'

       ,@Nom_Base                = 'MaBase'

       ,@Login                   = 'MonLogin'

       ,@PassWord                = 'MonMotDePasse'

       ,@Chemin_ScriptsSQL        = '\\MesFichiers\MesScripts\' --

       ,@ExecScript               = 0

       ,@TypeBackup               =NULL

NB : Pour désactiver le message "Changed database context to",passer le parametre -m1 à la commande sqlcmd

ou rajouter au début du fichier de script l'instruction :setvar SQLCMDERRORLEVEL 1

Exemple :

:setvar SQLCMDERRORLEVEL 1

USE [MaBase]

*/

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

DECLARE

       -- Add the parameters for the stored procedure here

       @Instance_SQLServer       AS VARCHAR(150)

       ,@Nom_Base                 AS VARCHAR(150)

       ,@Login                    AS VARCHAR(35)=NULL

       ,@PassWord                 AS VARCHAR(35)=NULL

       ,@Chemin_ScriptsSQL        AS VARCHAR(MAX)

       ,@ExecScript               AS BIT= 0

       ,@TypeBackup               AS VARCHAR(35)=NULL/*Valeurs possibles : BASE : Le process effectue le backup selon le type de récupération de la base.

FULL, DIFF, LOG, FULLLOG, DIFFLOG, FULLDIFFLOG

NULL : Aucun backup ne sera généré

                                                                                 */                                                                                                               

BEGIN

       SET NOCOUNTON;

       IF EXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ListeFichiersSQL]'))

             BEGIN

                    DROPTABLE #ListeFichiersSQL

             END

       IF EXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ListeFichiersRPT]'))

             BEGIN

                    DROPTABLE #ListeFichiersRPT

             END

       IF EXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ListeFichiersPWD]'))

             BEGIN

                    DROPTABLE #ListeFichiersPWD

             END

       IF EXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Journal]'))

             BEGIN

                    DROPTABLE #Journal

             END

       IF EXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ListeFichiersERR]'))

             BEGIN

                    DROPTABLE #ListeFichiersERR

             END

       CREATE TABLE #ListeFichiersSQL

       (

             Num_Lig                          INTIDENTITY (1,1),

             ExecFichierSQL                    BIT                               NULLDEFAULT 1,    

             DateDebExec               DATETIME                   NULL,

             DateFinExec               DATETIME                   NULL,

             NomFichierSQL              VARCHAR(800)        NULL,

             NomFichierRPT              VARCHAR(800)        NULL,

             NomFichierERR              VARCHAR(800)        NULL,

             EtatDeploiement                   VARCHAR(50)                DEFAULT'Script non déployé',

             MessageErreur              VARCHAR(MAX)        NULL

       )

       CREATE NON CLUSTERED INDEX [IDX_#ListeFichiersSQL_NomFichierRPT] ON #ListeFichiersSQL

       (

             NomFichierRPT ASC

       )

       CREATETABLE #ListeFichiersRPT

       (

             NomFichierRPT              VARCHAR(800)NULL

       )

       CREATENONCLUSTEREDINDEX [IDX_#ListeFichiersRPT_NomFichierRPT] ON #ListeFichiersRPT

       (

             NomFichierRPT ASC

       )

       CREATETABLE #ListeFichiersPWD

       (

             NomFichierPWD              VARCHAR(800)NULL

       )

       CREATENONCLUSTEREDINDEX [IDX_#ListeFichiersPWD_NomFichierPWD] ON #ListeFichiersPWD

       (

             NomFichierPWD ASC

       )

       CREATETABLE #Journal

       (

       Num_Lig                                 INTIDENTITY (1,1),

       LigneJournal               VARCHAR(1500)

       )

       CREATETABLE #ListeFichiersERR

       (

             NomFichierERR              VARCHAR(800)NULL

       )

       DECLARE @Requete                              NVARCHAR(3000)

                    ,@FichierSQL                      VARCHAR(800)

                    ,@NomFichierSQL                         VARCHAR(800)

                    ,@NomFichierRPT                         VARCHAR(800)

                    ,@NomFichierERR                         VARCHAR(800)

                    ,@NomFichierPWD                         VARCHAR(800)

                    ,@NomCompletFichierSQL            VARCHAR(800)

                    ,@NomFichierRetour                VARCHAR(800)

                    ,@Nbre_Lig                              INT

                    ,@Num_Lig                               INT

                    ,@ExecFichierSQL                  BIT

                    ,@MessageErreur                         VARCHAR(MAX)

                    ,@DateDebExec                     DATETIME                  

                    ,@DateFinExec                     DATETIME

                    ,@LocalHot                              VARCHAR(50)

                    ,@Environnement_Serveur           VARCHAR(150)

                    ,@Environnement_Param             VARCHAR(150)

 

IF CHARINDEX(':\',@Chemin_ScriptsSQL)<> 0

       BEGIN

SELECT

@Chemin_ScriptsSQL='\\MesFichiers\MesScripts\'+SUBSTRING(@Chemin_ScriptsSQL,CHARINDEX(':\',@Chemin_ScriptsSQL)+2,LEN(@Chemin_ScriptsSQL))

       END

 


       SELECT @LocalHot =QUOTENAME(@@SERVERNAME)

       SELECT @Nom_Base =REPLACE(REPLACE(@Nom_Base,'[',''),']','')

       IFCHARINDEX('\',REVERSE(@Chemin_ScriptsSQL),1)<>1

             BEGIN

                    SELECT @Chemin_ScriptsSQL = @Chemin_ScriptsSQL +'\'

             END

       --Récupérer la liste des fichiers PWD

       SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.pwd" /B'

       INSERTINTO #ListeFichiersPWD(NomFichierPWD)

       EXECMaster..xp_cmdshell@Requete

       --Générer dynamiquement le compte SQL et le mot de passe

       IFISNULL(@Login,'')=''ORISNULL(@PassWord,'')=''

             BEGIN

                    IF (ISNULL(@Login,'')=''ORISNULL(@PassWord,'')='')AND @ExecScript=1 ANDNOTEXISTS(SELECT NomFichierPWD FROM #ListeFichiersPWD WHERE NomFichierPWD LIKE'%.pwd')

                           BEGIN

                                       

                                  SELECT   @Login='LoginDeploy'+CAST((DATEPART(DAY,GETDATE())+DATEPART(YEAR,GETDATE())+DATEPART(MONTH,GETDATE())*DATEPART(HOUR,GETDATE()))*DATEPART(MINUTE,GETDATE())ASVARCHAR(8))

                                               ,@PassWord ='MotDePasse'+CAST(RAND()ASVARCHAR(50))

                                  SELECT @PassWord =REPLACE(@PassWord,'.','_')

                                  SELECT @Requete ='''USE [master]

                                  IF EXISTS (SELECT [name] FROM master.sys.server_principals WHERE type_desc=''''SQL_LOGIN'''' AND [name]='''''+ @Login +''''')

                                        BEGIN

                                               DROP LOGIN '+QUOTENAME(@Login)

                                  +'    END

                                  CREATE LOGIN '+QUOTENAME(@Login)+' WITH PASSWORD=N'''''+ @PassWord +''''', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

                                  ALTER SERVER ROLE [sysadmin] ADD MEMBER '+QUOTENAME(@Login)+''';'

                                  SELECT @Requete ='exec '+QUOTENAME(@Instance_SQLServer)+'.[master].[dbo].[usp_exec_script] '+ @Requete

                                  EXEC (@Requete)

                                  --Générer le fichier du password

                                  SELECT @MessageErreur = @Login +'@'+ @PassWord

                                        ,@NomFichierPWD = @Login +'@'+ @PassWord +'.pwd'

                                  SELECT @NomFichierPWD ='"'+ @Chemin_ScriptsSQL + @NomFichierPWD +'"'

                                  SELECT @Requete ='ECHO '+ @MessageErreur +' >> '+ @NomFichierPWD

                                 

                                  EXECMaster..xp_cmdshell@Requete

                           END

                    ELSE

                           BEGIN

                                  SELECT @NomFichierPWD=NomFichierPWD FROM #ListeFichiersPWD WHERE NomFichierPWD LIKE'%.pwd'

                                  SELECT @Login=SUBSTRING(@NomFichierPWD,1 ,CHARINDEX('@',@NomFichierPWD,1)-1)

                                  SELECT @PassWord=SUBSTRING(@NomFichierPWD,CHARINDEX('@',@NomFichierPWD,1)+1 ,LEN(@NomFichierPWD))

                                  SELECT @PassWord=SUBSTRING(@PassWord,1 ,CHARINDEX('.pwd',@PassWord,1)-1)

                           END

      

             END

       --Récupérer la liste des fichiers ERR

       SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.err" /B'

       INSERTINTO #ListeFichiersERR(NomFichierERR)

       EXECMaster..xp_cmdshell@Requete

            

       --Récupérer la liste des fichiers RPT

       SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.rpt" /B'

       INSERTINTO #ListeFichiersRPT(NomFichierRPT)

       EXECMaster..xp_cmdshell@Requete

       SELECT @Requete =NULL

       IF @TypeBackup LIKE'%FULL%'OR @TypeBackup LIKE'%DIFF%'OR @TypeBackup LIKE'%LOG%'

             BEGIN

                    SELECT @Requete ='SELECT 0 AS NumCommand,'''''''' AS Command'

                    --Forcer le backup FULL

                    IF @TypeBackup LIKE'%FULL%'

                           BEGIN

                                  SELECT @Requete = @Requete +' UNION SELECT TOP 1 1 AS NumCommand,Command FROM [msdb].[dbo].[sysjobsteps]

                                  WHERE command LIKE ''''%FULL_BACKUP%''''

                                  UNION

                                  SELECT 2 AS NumCommand,''''GO'''' AS Command'

                           END

                    IF @TypeBackup LIKE'%DIFF%'

                           BEGIN

                                  SELECT @Requete = @Requete +' UNION SELECT TOP 1 3 AS NumCommand,Command FROM [msdb].[dbo].[sysjobsteps]

                                  WHERE command LIKE ''''%DIFF_BACKUP%''''

                                  UNION

                                  SELECT 4 AS NumCommand,''''GO'''' AS Command'

                           END

                    IF @TypeBackup LIKE'%LOG%'

                           BEGIN

                                  SELECT @Requete = @Requete +' UNION SELECT TOP 1 5 AS NumCommand,Command FROM [msdb].[dbo].[sysjobsteps]

                                  WHERE command LIKE ''''%LOG_BACKUP%''''

                                  UNION

                                  SELECT 6 AS NumCommand,''''GO'''' AS Command'

                           END

                   

                    SELECT @Requete ='SELECT Command FROM ( '+ @Requete +' ) T2 WHERE ISNULL(Command,'''''''')<>'''''''' ORDER BY NumCommand'

             END

       ELSE

             --Selon le mode de récupération de la base, construire la requête du backup DIFF+LOG ou seulement DIFF

             BEGIN

                    SELECT @Requete ='SELECT Command FROM (

                    SELECT TOP 1 1 AS NumCommand,LTRIM(RTRIM(Command)) AS Command FROM [msdb].[dbo].[sysjobsteps]

                    WHERE command LIKE ''''%DIFF_BACKUP%'''' AND EXISTS(SELECT [name] FROM [master].[sys].[databases] WHERE [name]='''''+ @Nom_Base +''''')'

                    +' UNION

                    SELECT TOP 1 2 AS NumCommand,''''GO'''' AS Command FROM [master].[sys].[databases] WHERE [name]='''''+ @Nom_Base +''''''

                    +' UNION

                    SELECT TOP 1 3 AS NumCommand,LTRIM(RTRIM(Command)) AS Command FROM [msdb].[dbo].[sysjobsteps]

                    WHERE command LIKE ''''%LOG_BACKUP%'''''+' AND (SELECT COUNT(recovery_model) FROM [master].[sys].[databases] WHERE recovery_model=1 AND [name]='''''+ @Nom_Base +''''')=1'

                    +' UNION

                    SELECT 4 AS NumCommand,''''GO'''' AS Command FROM [msdb].[dbo].[sysjobsteps]

                    WHERE command LIKE ''''%LOG_BACKUP%'''''+' AND (SELECT COUNT(recovery_model) FROM [master].[sys].[databases] WHERE recovery_model=1 AND [name]='''''+ @Nom_Base +''''')=1'

                    +') T1 ORDER BY NumCommand'

             END

       SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQLServer THENN'SELECT Command FROM OPENQUERY('+QUOTENAME(@Instance_SQLServer)+','''ELSE''END+ @Requete

       SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQLServer THEN @Requete +N''')'ELSEREPLACE(@Requete,'''''','''')END

            

       INSERTINTO #Journal(LigneJournal)

       EXECUTEsp_executesql@Requete

       UPDATE #Journal

       SET

             LigneJournal='exec master..usp_MaProcedureBackupBase '+SUBSTRING(LigneJournal,CHARINDEX('''',LigneJournal),LEN(LigneJournal)-CHARINDEX('''',LigneJournal))

       WHERE LigneJournal<>'GO'

                   

                   

       UPDATE #Journal

       SET

             LigneJournal = LigneJournal +CASEWHENCHARINDEX('''',RTRIM(LTRIM(REVERSE(LigneJournal))),1)<=4 THEN','''ELSE''','''END+ @Nom_Base +''';'

       WHERE LigneJournal<>'GO'

       --Récupérer l'environnement passé en paramètre

       SELECT @Environnement_Param =CASE      WHEN @Chemin_ScriptsSQL LIKE'%\MPROD\%'THEN'PRODUCTION'

                                                                   WHEN @Chemin_ScriptsSQL LIKE'%\MHOM\%'THEN'HOMOLOGATION'

                                                                   WHEN @Chemin_ScriptsSQL LIKE'%\MDEV\%'THEN'DEVELOPPEMENT'

                                                      ELSENULL

                                                      END

       --Récupérer l'environnement du serveur

       SELECT@Environnement_Serveur =CASEWHEN LigneJournal LIKE'%BACKUP_PROD%'THEN'PRODUCTION'

                                  WHEN LigneJournal LIKE'%BACKUP_HOM%'THEN'HOMOLOGATION'

                                  WHEN LigneJournal LIKE'%BACKUP_DEV%'THEN'DEVELOPPEMENT'

                                  ELSENULL

                                  END

       FROM #Journal WHERE LigneJournal LIKE'%BACKUP_%'

       /*Vérifier que l'environnement du serveur de déploiement

       et l'environnement du chemin des scripts à déployer se correspondent bien

       */

       IF @Environnement_Serveur = @Environnement_Param ANDNOTEXISTS(SELECT NomFichierERR FROM #ListeFichiersERR WHERE NomFichierERR LIKE'%.err')

             BEGIN

                    --Générer le fichier du backup

                    IF @TypeBackup LIKE'%BASE%'OR @TypeBackup LIKE'%FULL%'OR @TypeBackup LIKE'%DIFF%'OR @TypeBackup LIKE'%LOG%'

                           BEGIN

                                  SELECT @Num_Lig = 1,@Nbre_Lig=COUNT(LigneJournal), @NomFichierRetour='"'+ @Chemin_ScriptsSQL +'00 - script backup'+ @Nom_Base +'.sql"'FROM #Journal

                                  WHILE @Num_Lig <= @Nbre_Lig

                                        BEGIN

                                               SELECT @MessageErreur=LigneJournal FROM #Journal WHERE Num_Lig=@Num_Lig

                                               IF @Num_Lig=1

                                                      BEGIN

                                                             SELECT @Requete ='ECHO '+ @MessageErreur +' > '+ @NomFichierRetour

                                                      END

                                               ELSE

                                                      BEGIN

                                                             SELECT @Requete ='ECHO '+ @MessageErreur +' >> '+ @NomFichierRetour

                                                      END   

                                                                         

                                               EXECMaster..xp_cmdshell@Requete

                                               SELECT @Num_Lig = @Num_Lig + 1

                                        END

                   

                           END

                    --Récupérer la liste des scripts SQL          

                    SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.sql" /B'

            

                    INSERTINTO #ListeFichiersSQL(NomFichierSQL)

                    EXECMaster..xp_cmdshell@Requete

                    UPDATE #ListeFichiersSQL

                    SET

                           NomFichierRPT =SUBSTRING(NomFichierSQL,1,LEN(NomFichierSQL)-3)+'rpt'

                       ,NomFichierERR =SUBSTRING(NomFichierSQL,1,LEN(NomFichierSQL)-3)+'err'

                    --Activer les scripts à lancer

                    UPDATE T1

                    SET

                           ExecFichierSQL=0

                    FROM #ListeFichiersSQL T1

                    JOIN #ListeFichiersRPT T2 ON T2.NomFichierRPT=T1.NomFichierRPT

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

                    WHILE @Num_Lig <= @Nbre_Lig AND @ExecScript=1

                           BEGIN

                                  SELECT @NomFichierSQL= NomFichierSQL,@NomFichierRPT=NomFichierRPT,@ExecFichierSQL=ExecFichierSQL,@NomFichierERR=NomFichierERR FROM #ListeFichiersSQL WHERE Num_Lig=@Num_Lig

                                  IF @NomFichierSQL ISNOTNULLAND @NomFichierRPT ISNOTNULLAND @ExecFichierSQL=1

                                        BEGIN

                                               SELECT @NomCompletFichierSQL ='"'+ @Chemin_ScriptsSQL + @NomFichierSQL +'"'

                                                             ,@NomFichierRetour ='"'+ @Chemin_ScriptsSQL + @NomFichierRPT +'"'

                                               SELECT @DateDebExec=GETDATE()

                                              

                                              

                                               --Faire le traitement avec sqlcmd

                                               BEGIN

                                                      IFISNULL(@Login,'')=''ORISNULL(@PassWord,'')=''

                                                             BEGIN

                                                                   SELECT @Requete ='sqlcmd -V1 -I -u -E -S '+ @Instance_SQLServer +CASEWHEN @NomFichierSQL LIKE'%script%backup%'THEN' -m0 'ELSE' -m1 'END+' -d '+ @Nom_Base +' -i '+ @NomCompletFichierSQL +' -r0 -b -o '+ @NomFichierRetour

                                                             END

                                                      ELSE

                                                             BEGIN

                                                                   SELECT @Requete ='sqlcmd -V1 -I -u -U '+ @Login +' -P '+ @PassWord +' -S '+ @Instance_SQLServer +CASEWHEN @NomFichierSQL LIKE'%script%backup%'THEN' -m0 'ELSE' -m1 'END+' -d '+ @Nom_Base +' -i '+ @NomCompletFichierSQL +' -r0 -b -o '+ @NomFichierRetour

                                                             END

                                               END

                                               INSERTINTO #Journal(LigneJournal)

                                               EXECMaster..xp_cmdshell@Requete

                                               SELECT @DateFinExec=GETDATE()

                                               -- Intercepter le message d'ereur

                                               SELECT @Requete ='TYPE '+ @NomFichierRetour

                                 

                                               TRUNCATETABLE #Journal

                                               INSERTINTO #Journal(LigneJournal)

                                               EXECMaster..xp_cmdshell@Requete

                                               SELECT @MessageErreur =''

                                               SELECT @MessageErreur=@MessageErreur +' '+ISNULL(LigneJournal,'')FROM #Journal

                                               UPDATE #ListeFichiersSQL

                                               SET

                                                      MessageErreur= @MessageErreur

                                                      ,DateDebExec=@DateDebExec

                                                      ,DateFinExec=@DateFinExec

                                               WHERE Num_Lig=@Num_Lig

            

                                               IFEXISTS(SELECT LigneJournal FROM #Journal WHERE LigneJournal LIKE'%level_[1-9]%'OR LigneJournal LIKE'%Niveau_[1-9]%'

                                               OR LigneJournal LIKE'%msg_[1-9]%'OR LigneJournal LIKE'%Error%'OR LigneJournal LIKE'%Erreur%'OR LigneJournal LIKE'%cannot%')

                                               OR(@NomFichierSQL LIKE'%script%backup%'ANDNOTEXISTS(SELECT LigneJournal FROM #Journal WHERE LigneJournal LIKE+'%'+ @Nom_Base +'%.bak%'))

                                                      BEGIN

                                                             --Arrêter le déploiement en cas d'erreur

                                                            UPDATE #ListeFichiersSQL

                                                             SET

                                                                   EtatDeploiement='Echec déploiement'

                                                             WHERE Num_Lig=@Num_Lig

                                                             --Renommer le fichier en .ERR

                                                             SELECT @Requete ='RENAME '+ @NomFichierRetour +' "'+ @NomFichierERR +'"'

                                                             EXECmaster..xp_cmdshell@Requete

                                                            

                                                             SELECT @Num_Lig = @Nbre_Lig

                                                      END

                                               ELSE

                                                      BEGIN

                                                             UPDATE #ListeFichiersSQL

                                                             SET

                                                                   EtatDeploiement='Succès déploiement'

                                                             WHERE Num_Lig=@Num_Lig

                                                      END

                                               IF (SELECTCOUNT(*)FROM #Journal)=1 ANDEXISTS(SELECT LigneJournal FROM #Journal WHERE LigneJournal ISNULL)

                                                      BEGIN

                                                             --SELECT @Requete = 'ECHO Commandes reussies >>' + @NomFichierRetour

                                                             --EXEC Master..xp_cmdshell @Requete

                                                             IFISNULL(@Login,'')=''ORISNULL(@PassWord,'')=''

                                                                   BEGIN

                                                                          SELECT @Requete ='sqlcmd -V1 -I -u -E -S '+ @Instance_SQLServer +CASEWHEN @NomFichierSQL LIKE'%script%backup%'THEN' -m0 'ELSE' -m1 'END+' -d '+ @Nom_Base +' -q "PRINT ''Commandes réussies''"'+' -r0 -b -o '+ @NomFichierRetour

                                                                   END

                                                             ELSE

                                                                   BEGIN

                                                                          SELECT @Requete ='sqlcmd -V1 -I -u -U '+ @Login +' -P '+ @PassWord +' -S '+ @Instance_SQLServer +CASEWHEN @NomFichierSQL LIKE'%script%backup%'THEN' -m0 'ELSE' -m1 'END+' -d '+ @Nom_Base +' -q "PRINT ''Commandes réussies''" '+' -r0 -b -o '+ @NomFichierRetour

                                                                   END

                                                             INSERTINTO #Journal(LigneJournal)

                                                             EXECMaster..xp_cmdshell@Requete

                                                             UPDATE #ListeFichiersSQL

                                                             SET

                                                                   MessageErreur='Commandes réussies'

                                                             WHERE Num_Lig=@Num_Lig

                                                      END

                                        END

                                  SELECT @Num_Lig = @Num_Lig + 1

                           END

                    SELECT*FROM #ListeFichiersSQL WHERE NomFichierSQL ISNOTNULL

             END

       ELSE

             BEGIN

                    IFISNULL(@Environnement_Serveur,'INCONNU')<>ISNULL(@Environnement_Param,'INCONNU')

                           BEGIN

                                  SELECTUPPER('!!!!! La source des scripts à déployer : ')+ISNULL(@Environnement_Param,'INCONNU.')

                                  +CHAR(13)+UPPER('Le serveur de déploiement : ')+ISNULL(@Environnement_Serveur,'INCONNU.')

                                  +CHAR(13)+UPPER('Le déploiement ne peut se faire que sur un seul environnement !!!!!')

                           END

                    IFEXISTS(SELECT NomFichierERR FROM #ListeFichiersERR WHERE NomFichierERR LIKE'%.err')

                           BEGIN

                                  SELECT'La présence des fichiers avec extension .ERR empêche le déploiement des scripts.'

                                        +CHAR(13)+'Renommer le ficher .ERR en .RPT permet de déploiement des scripts suivants.'

                                         +CHAR(13)+'La supression du fichierr .ERR et/ou RPT permet le redéploiement des scripts.'

                           END

             END

       --SELECT * FROM #Journal

       --SELECT * FROM #ListeFichiersRPT

       --Récupérer la liste des fichiers PWD

       TRUNCATETABLE #ListeFichiersPWD

       SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.pwd" /B'

       INSERTINTO #ListeFichiersPWD(NomFichierPWD)

       EXECMaster..xp_cmdshell@Requete

       IF @ExecScript=1 ANDEXISTS(SELECT NomFichierPWD FROM #ListeFichiersPWD WHERE NomFichierPWD LIKE'%.pwd')

             BEGIN

                    --Récupérer la liste des fichiers ERR

                    TRUNCATETABLE #ListeFichiersERR

                    SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.err" /B'

                    INSERTINTO #ListeFichiersERR(NomFichierERR)

                    EXECMaster..xp_cmdshell@Requete

                    IFNOTEXISTS(SELECT NomFichierERR FROM #ListeFichiersERR WHERE NomFichierERR LIKE'%.err')

                           BEGIN

                                  --Supprimer le compte SQL crée dynamiquement

                                  SELECT @Requete ='''USE [master]

                                  IF EXISTS (SELECT [name] FROM master.sys.server_principals WHERE type_desc=''''SQL_LOGIN'''' AND [name]='''''+ @Login +''''')

                                        BEGIN

                                               DROP LOGIN '+QUOTENAME(@Login)

                                  +'    END '';'

                                  SELECT @Requete ='exec '+QUOTENAME(@Instance_SQLServer)+'.[master].[dbo].[usp_exec_script] '+ @Requete

                                  EXEC (@Requete)

                                  --Supprimer le fichier de login

                                  SELECT @NomFichierPWD ='"'+ @Chemin_ScriptsSQL +'*.pwd"'

                                  SELECT @Requete ='DEL '+ @NomFichierPWD +' /Q '

            

                                  EXECMaster..xp_cmdshell@Requete

                           END

             END

       DROPTABLE #ListeFichiersSQL

       DROPTABLE #Journal

       DROPTABLE #ListeFichiersRPT

       DROPTABLE #ListeFichiersERR

END

GO

/*Sur la base master de chaque instance créer la procédure stockée [dbo].[usp_exec_script] */

USE [master]

GO

CREATE PROCEDURE [dbo].[usp_exec_script](@requete_sql varchar(max))

AS

BEGIN

       EXEC (@requete_sql)

END

GO