DESCRIPTION
1. copy all backup files from a source folder into remote FTP server
2. move all backup files from a source folder into another local folder
3. Tested in SQL Server 2005
AUTHOR
Siddique
DATE
30/11/2007
************************************************************************************/
CREATE PROCEDURE [dbo].[P_FtpPutFile_ReportServer]
(
@FTPServer varchar(128)
,@FTPUser nvarchar(128)
,@FTPPWD nvarchar(128)
,@FTPPath nvarchar(128)
,@SourcePath nvarchar(128)
,@SourceFile nvarchar(128)
,@workdir nvarchar(128)
,@DestPath nvarchar(128)
,@binaryFile bit = NULL
)
AS
BEGIN
SET NOCOUNT ON;
IF(@binaryFile IS NULL)
begin
SET @binaryFile = 1
end
DECLARE @cmd varchar(1000) — command to execute
DECLARE @workfilename varchar(20) — file to store ftp command batch
SET @workfilename = ‘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, ‘|’, ‘^|’),'<','^’,’^>’)
— insert FTP servername to @workfilename
SET @cmd = ‘echo open ‘ + @FTPServer + ‘ > ‘ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
— insert FTP username to @workfilename
SET @cmd = ‘echo ‘ + @FTPUser + ‘>> ‘ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
— insert FTP password to @workfilename
SET @cmd = ‘echo ‘ + @FTPPWD + ‘>> ‘ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
IF @binaryFile = 1
begin
— set the file transfer type to binary
SET @cmd = ‘echo bin’ + ‘ >> ‘ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
end
— change the working directory on the remote computer
SET @cmd = ‘echo cd ‘ + @FTPPath + ‘ >> ‘ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
— copy all files of a type @SourceFile from @SourcePath into @FTPPath
SET @cmd = ‘echo mput ‘ + @SourcePath + @SourceFile + ‘ >> ‘ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
— end the FTP session with the remote computer and exit ftp
SET @cmd = ‘echo quit’ + ‘ >> ‘ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
— execute the @workfilename (i – turn off interactive prompting for the mput command)
SET @cmd = ‘ftp -i -s:’ + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd–, no_output
— move all files of a type @SourceFile from @SourcePath into @DestPath (Y – suppress prompting to overwrite)
SET @cmd = ‘move /Y ‘ + @SourcePath + @SourceFile + ‘ ‘ + @DestPath
EXEC master..xp_cmdshell @cmd, no_output
END
IntelliDB 