CREATE PROCEDURE [dbo].[SendFileUsingFTP]

@FTPServer varchar(128),
@FTPServerPort varchar(10),
@FTPUser varchar(128),
@FTPPwd varchar(128),
@SourcePath varchar(128),
@SourceFiles varchar(128),
@DestPath varchar(128),
@FTPMode varchar(10),
@DeleteSource tinyint

AS

EXEC sp_configure ‘show advanced options’, 1

— To update the currently configured value for advanced options.

RECONFIGURE

— To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

— To update the currently configured value for this feature.

RECONFIGURE

— FTP attributes.
IF (@FTPServerPort IS NOT NULL AND @FTPServerPort <> ’21’ AND @FTPServerPort <> ”) SET @FTPServer = @FTPServer + ‘:’ + @FTPServerPort

DECLARE @cmd varchar(1000)
DECLARE @workfile varchar(128)
DECLARE @queryfile varchar(128)
DECLARE @nowstr varchar(25)

— Get the %TEMP% environment variable.
DECLARE @tempdir varchar(128)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell ‘echo %temp%’
SET @tempdir = (SELECT top 1 info FROM #tempvartable)
IF RIGHT(@tempdir, 1) <> ‘\’ SET @tempdir = @tempdir + ‘\’
DROP TABLE #tempvartable

— Generate @workfile
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ‘ ‘, ‘_’), ‘:’, ‘-‘)
SET @workfile = ‘FTP_SPID’ + convert(varchar(128), @@spid) + ‘_’ + @nowstr + ‘.txt’
SET @queryfile = ‘FTP_RES’ + convert(varchar(128), @@spid) + ‘_’ + @nowstr + ‘.html’

— Deal with special chars for echo commands.
select @FTPServer = replace(replace(replace(@FTPServer, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
select @FTPUser = replace(replace(replace(@FTPUser, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
select @FTPPwd = replace(replace(replace(@FTPPwd, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
select @DestPath = replace(replace(replace(@DestPath, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
IF RIGHT(@SourcePath, 1) <> ‘\’ SET @SourcePath = @SourcePath + ‘\’

— Build the FTP script file.
select @cmd = ‘echo ‘ + ‘open ‘ + @FTPServer + ‘ > ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

select @cmd = ‘echo ‘ + @FTPUser + ‘>> ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

select @cmd = ‘echo ‘ + @FTPPwd + ‘>> ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

IF LEN(@DestPath) > 0
BEGIN

IF LEFT(@DestPath, 1) <> ‘/’ SET @DestPath = ‘/’ + @DestPath
select @cmd = ‘echo ‘ + ‘cd “‘ + @DestPath + ‘” >> ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

END

select @cmd = ‘echo ‘ + ‘prompt ‘ + ‘ >> ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

IF LEN(@FTPMode) > 0
BEGIN

select @cmd = ‘echo ‘ + @FTPMode + ‘ >> ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

END

select @cmd = ‘echo ‘ + ‘mput “‘ + @SourcePath + @SourceFiles + ‘” >> ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

select @cmd = ‘echo ‘ + ‘quit’ + ‘ >> ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

— Execute the FTP command via script file.
select @cmd = ‘ftp -s:’ + @tempdir + @workfile
create table #a (id int identity(1,1), s varchar(1000))
insert #a
EXEC master..xp_cmdshell @cmd

— Clean up.
drop table #a
select @cmd = ‘del ‘ + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

— Delete source table
If @DeleteSource = 1
BEGIN

select @cmd = ‘del ‘ + @SourcePath + @SourceFiles
EXEC master..xp_cmdshell @cmd

END

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s