SQL Script to Send a File by FTP

  1
  2USE master;
  3 PRINT '-- Server name: '+@@servername
  4
  5IF EXISTS (
  6         SELECT *
  7         FROM sysobjects
  8         WHERE id = object_id(N'[dbo].[sp_FtpSendFile]')
  9             AND OBJECTPROPERTY(id, N'IsProcedure') = 1
 10         )
 11     DROP PROCEDURE [dbo].[sp_FtpSendFile]
 12 GO
 13
 14/************************************************************
 15
 16Description : Send a file by FTP
 17
 18 @FTPServer      The host name.    ftp.example.com
 19 @FTPUser        The username for the FTP site.
 20 @FTPPWD         The password for the FTP site.
 21 @FTPPath        The ftp subfolder within the FTP site to place the file. Make sure to use forward slashes and leave a trailing slash.
 22 @FTPFileName    (optional) The filename to write within FTP. Typically the same as the source file name.
 23 @FTPPASV        (optional) Use passive mode
 24 @SourcePath     The local path to the directory that contains the source file. Make sure to have a trailing slash. c:\MyLocalPath\
 25 @SourceFile     The local source file to ftp.
 26 @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%
 27
 28*************************************************************/
 29 CREATE PROCEDURE sp_FtpSendFile
 30
 31     -- Remote
 32      @FTPServer VARCHAR(128)
 33     ,@FTPUser VARCHAR(128)
 34     ,@FTPPWD VARCHAR(128)
 35     ,@FTPPath VARCHAR(128)
 36     ,@FTPFileName VARCHAR(128) = NULL  -- (optional)
 37     ,@FTPPASV INT = 1                  -- (optional)
 38
 39    -- Local
 40     ,@SourcePath VARCHAR(128)
 41     ,@SourceFile VARCHAR(128)
 42     ,@workdir VARCHAR(128) = '%TEMP%'  -- (optional)
 43
 44    -- debug
 45     ,@debug int = 0                    -- (optional)
 46
 47AS
 48 BEGIN
 49
 50/*
 51
 52 -- sandbox
 53
 54DECLARE
 55      @FTPServer VARCHAR(128)
 56     ,@FTPUser VARCHAR(128) = ''
 57     ,@FTPPWD VARCHAR(128)
 58     ,@FTPPath VARCHAR(128)
 59     ,@FTPFileName VARCHAR(128) = NULL -- (optional)
 60     ,@FTPPASV INT = 1 -- (optional)
 61     ,@SourcePath VARCHAR(128)
 62     ,@SourceFile VARCHAR(128)
 63     ,@workdir VARCHAR(128) = '%TEMP%' -- (optional)
 64     ,@debug int = 0
 65
 66SELECT
 67     @FTPServer = 'ftp.mysite.com' ,
 68     @FTPUser = 'myusername' ,
 69     @FTPPWD = 'mypassword' ,
 70     @FTPPath = '/' ,
 71     @SourcePath = 'K:\temp\Backup\' ,
 72     @SourceFile = 'myFile.bak' ,
 73     @debug = 0
 74
 75*/
 76
 77    SET nocount on
 78     DECLARE @cmd VARCHAR(1000)
 79     DECLARE @workfilename VARCHAR(128)
 80     DECLARE @noNull table (r NVARCHAR(MAX))
 81
 82    SET @workfilename =  @workdir + 'ftpcmd.txt'
 83
 84    -- deal with special characters for echo commands
 85     SET @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'), '<', '^<'), '>', '^>')
 86     SET @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'), '<', '^<'), '>', '^>')
 87     SET @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'), '<', '^<'), '>', '^>')
 88     SET @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'), '<', '^<'), '>', '^>')
 89
 90    SET @cmd = 'echo open ' + @FTPServer + ' > ' + @workfilename
 91     INSERT @noNull EXEC master..xp_cmdshell @cmd
 92
 93    SET @cmd = 'echo ' + @FTPUser + '>> ' + @workfilename
 94     INSERT @noNull EXEC master..xp_cmdshell @cmd
 95
 96    SET @cmd = 'echo ' + @FTPPWD + '>> ' + @workfilename
 97     INSERT @noNull EXEC master..xp_cmdshell @cmd
 98
 99    IF (@FTPPASV = 1)
100     BEGIN
101         SET @cmd = 'echo quote PASV >> ' + @workfilename
102         INSERT @noNull EXEC master..xp_cmdshell @cmd
103     END
104
105    SET @cmd = 'echo cd '+ @FTPPath + '>> ' + @workfilename
106     INSERT @noNull EXEC master..xp_cmdshell @cmd
107
108    SET @cmd = 'echo binary >> ' + @workfilename
109     INSERT @noNull EXEC master..xp_cmdshell @cmd
110
111    SET @cmd = 'echo put ' + @SourcePath + @SourceFile + ' ' + ISNULL(@FTPFileName, '') + ' >> ' + @workfilename
112     INSERT @noNull EXEC master..xp_cmdshell @cmd
113
114    SET @cmd = 'echo dir ' + ISNULL(@FTPFileName, @SourceFile) + ' >> ' + @workfilename
115     INSERT @noNull EXEC master..xp_cmdshell @cmd
116
117    SET @cmd = 'echo quit' + ' >> ' + @workfilename
118     INSERT @noNull EXEC master..xp_cmdshell @cmd
119
120    SET @cmd = 'ftp -s:' + @workfilename
121     DECLARE @a TABLE (
122         id INT identity(1, 1)
123         ,s VARCHAR(1000)
124         );
125
126    IF (@debug = 1)
127     BEGIN
128         SET @cmd = 'TYPE ' +  @workfilename
129         EXEC master..xp_cmdshell @cmd
130     END
131     ELSE
132     BEGIN
133     INSERT @a EXEC master..xp_cmdshell @cmd;
134         SELECT id, ouputtmp = s
135         FROM @a;
136     END
137 END
138 GO