SQL Script to send a file by FTP
Rédigé par Sozezzo - - Aucun commentaireThis Stored procedure send a file by FTP.
Check if SQL Agent has access to the file and FTP server.
The little trick is to create a file that will run. It can have conflicts easily because filename is always the same. Next version will be with random name.
Use this code carefully, it is not 100% safe.
USE master; PRINT '-- Server name: '+@@servername IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[sp_FtpSendFile]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) DROP PROCEDURE [dbo].[sp_FtpSendFile] GO /************************************************************ Description : Send a file by FTP @FTPServer The host name. ftp.example.com @FTPUser The username for the FTP site. @FTPPWD The password for the FTP site. @FTPPath The ftp subfolder within the FTP site to place the file. Make sure to use forward slashes and leave a trailing slash. @FTPFileName (optional) The filename to write within FTP. Typically the same as the source file name. @FTPPASV (optional) Use passive mode @SourcePath The local path to the directory that contains the source file. Make sure to have a trailing slash. c:\MyLocalPath\ @SourceFile The local source file to ftp. @workdir (optional) The working local directory. This is where the stored proc will temporarily write a command file containing the FTP commands it will execute. = %TEMP% *************************************************************/ CREATE PROCEDURE sp_FtpSendFile -- Remote @FTPServer VARCHAR(128) ,@FTPUser VARCHAR(128) ,@FTPPWD VARCHAR(128) ,@FTPPath VARCHAR(128) ,@FTPFileName VARCHAR(128) = NULL -- (optional) ,@FTPPASV INT = 1 -- (optional) -- Local ,@SourcePath VARCHAR(128) ,@SourceFile VARCHAR(128) ,@workdir VARCHAR(128) = '%TEMP%' -- (optional) -- debug ,@debug int = 0 -- (optional) AS BEGIN /* -- sandbox DECLARE @FTPServer VARCHAR(128) ,@FTPUser VARCHAR(128) = '' ,@FTPPWD VARCHAR(128) ,@FTPPath VARCHAR(128) ,@FTPFileName VARCHAR(128) = NULL -- (optional) ,@FTPPASV INT = 1 -- (optional) ,@SourcePath VARCHAR(128) ,@SourceFile VARCHAR(128) ,@workdir VARCHAR(128) = '%TEMP%' -- (optional) ,@debug int = 0 SELECT @FTPServer = 'ftp.mysite.com' , @FTPUser = 'myusername' , @FTPPWD = 'mypassword' , @FTPPath = '/' , @SourcePath = 'K:\temp\Backup\' , @SourceFile = 'myFile.bak' , @debug = 0 */ SET nocount on DECLARE @cmd VARCHAR(1000) DECLARE @workfilename VARCHAR(128) DECLARE @noNull table (r NVARCHAR(MAX)) SET @workfilename = @workdir + 'ftpcmd.txt' -- deal with special characters for echo commands SET @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'), '<', '^<'), '>', '^>') SET @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'), '<', '^<'), '>', '^>') SET @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'), '<', '^<'), '>', '^>') SET @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'), '<', '^<'), '>', '^>') SET @cmd = 'echo open ' + @FTPServer + ' > ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd SET @cmd = 'echo ' + @FTPUser + '>> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd SET @cmd = 'echo ' + @FTPPWD + '>> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd IF (@FTPPASV = 1) BEGIN SET @cmd = 'echo quote PASV >> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd END SET @cmd = 'echo cd '+ @FTPPath + '>> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd SET @cmd = 'echo binary >> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd SET @cmd = 'echo put ' + @SourcePath + @SourceFile + ' ' + ISNULL(@FTPFileName, '') + ' >> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd SET @cmd = 'echo dir ' + ISNULL(@FTPFileName, @SourceFile) + ' >> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd SET @cmd = 'echo quit' + ' >> ' + @workfilename INSERT @noNull EXEC master..xp_cmdshell @cmd SET @cmd = 'ftp -s:' + @workfilename DECLARE @a TABLE ( id INT identity(1, 1) ,s VARCHAR(1000) ); IF (@debug = 1) BEGIN SET @cmd = 'TYPE ' + @workfilename EXEC master..xp_cmdshell @cmd END ELSE BEGIN INSERT @a EXEC master..xp_cmdshell @cmd; SELECT id, ouputtmp = s FROM @a; END END GO