mars
2011
Un article de zinzineti
Pour réaliser sous SQL Server le download d’un fichier de backup via le protocole FTP.
– =======================================================
– Description : SQL SERVER – Envoi de fichier via FTP
– Auteur : Etienne ZINZINDOHOUE
/***************** Exemple d'utilisation *************
DECLARE @Result INT
EXEC P_FTP_GET
@FTPServer = N'ftp.xxxxxx.fr',
@FTPUser = N'e.zinzindohoue',
@FTPPwd =N'xxxxxx',
@FTPFile =N'/outgoing/ez/Backup_MaBase.bak',
@DestFile =N'c:\Backup_MaBase.bak',
@FileTemp =N'c:\temp\cmdftp_get.txt',
@Result = @Result OUTPUT
SELECT 'Resultat'= CASE WHEN @Result=1 THEN 'Transfert SUCCESSFULLY' ELSE 'ECHEC' END
*****************************************************/
– =======================================================
– Drop la procedure stockée si elle existe déjà dans la base
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_FTP_GET]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_FTP_GET]
GO
CREATE PROCEDURE P_FTP_GET
@FTPServer nvarchar(100) ,
@FTPUser nvarchar(100) ,
@FTPPWD nvarchar(100) ,
@FTPFile nvarchar(100) ,
@DestFile nvarchar(100) ,
@FileTemp nvarchar(100) ,
@Result INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @CMD nvarchar(4000)
DECLARE @TranferComplete INT
SET @Result = 0
– Traitement des caractres spciaux
SET @FTPServer = replace(replace(replace(@FTPServer, '|', '"|"'),'<','"<"'),'>','">"')
SET @FTPUser = replace(replace(replace(@FTPUser, '|', '"|"'),'<','"<"'),'>','">"')
SET @FTPPWD = replace(replace(replace(@FTPPWD, '|', '"|"'),'<','"<"'),'>','">"')
SET @FTPFile = replace(replace(replace(@FTPFile, '|', '"|"'),'<','"<"'),'>','">"')
SET @CMD = 'echo ' + 'open ' + @FTPServer + ' > ' + @FileTemp
EXEC master..xp_cmdshell @CMD
SET @CMD = 'echo ' + @FTPUser + '>> ' + @FileTemp
EXEC master..xp_cmdshell @CMD
SET @CMD = 'echo ' + @FTPPWD + '>> ' + @FileTemp
EXEC master..xp_cmdshell @CMD
SET @CMD = 'echo ' + 'get ' + @FTPFile + ' ' + @DestFile + ' >> ' + @FileTemp
EXEC master..xp_cmdshell @CMD
SET @CMD = 'echo ' + 'quit' + ' >> ' + @FileTemp
EXEC master..xp_cmdshell @CMD
SET @CMD = 'ftp -i -v -s:' + @FileTemp
–PRINT @CMD
—Insertion du resultat de la commande ftp dans la table @t
DECLARE @t table (id int identity(1,1), ftpmessage nvarchar(4000))
INSERT INTO @t
EXEC master..xp_cmdshell @CMD
– Test du resultat du transfert FTP
SELECT @TranferComplete = count(id) FROM @t WHERE ftpmessage LIKE '%Transfer complete%'
IF (@TranferComplete >= 1)
SET @Result = 1
GO
Pour faire l’opération inverse, c’est à dire un Upload voir mon billet intitulé « FTP – SQL Server (1/2) »
———————————————–
Etienne ZINZINDOHOUE
———————————————–