Generate a text file and FTP it using a SQL Stored Procedure

If you need a sql server stored procedure to generate a text file from a sql server table or view or query, and ftp it to someone, this code will work. NOTE: this requires the xp_cmdshell functionality in SQL Server to be turned on.

ALTER proc [dbo].[spFTP] as

--Step 1 - output data to text file

DECLARE @sqlCommand VARCHAR(1000)

DECLARE @filePath VARCHAR(100)

DECLARE @fileName VARCHAR(100)

SET @filePath = 'e:\yourfolder\'

SET @fileName = 'myfile_' +

+ CONVERT(VARCHAR, GETDATE(), 112) + '.txt'

SET @sqlCommand =

'SQLCMD -S (local) -E -d yourdatabasename -q "set nocount on select * from databasename.dbo.tablename" -o "' +

@filePath + @fileName +

'" -h-1 -s"," -W '

--NOTE: if you don't put "set nocount on " before your query, your text file will contain a record at the end that says "32 records affected".

--NOTE: the -h-1 means that you don't want to include headers in your text file

--NOTE: the -s"," makes it a comma delimited text file

--NOTE: the -W removes trailing spaces after each field

--NOTE: for guidance on these commands see http://technet.microsoft.com/en-us/library/ms162773.aspx

EXEC master..xp_cmdshell @sqlCommand

--Step 2 FTP it to someone

-- FTP_MPUT.sql (Written by John Buoro http://www.VirtualObjectives.com.au)

-- Transfer multiple files to an FTP server using MPUT.

DECLARE @FTPServer varchar(128)

DECLARE @FTPUser varchar(128)

DECLARE @FTPPwd varchar(128)

DECLARE @SourcePath varchar(128)

DECLARE @SourceFiles varchar(128)

DECLARE @DestPath varchar(128)

DECLARE @FTPMode varchar(10)

-- FTP attributes.

SET @FTPServer = '111.1.30.00'

SET @FTPUser = 'youruserid'

SET @FTPPwd = 'yourpassword'

SET @SourcePath = 'e:\yourfolder\'

SET @SourceFiles = @filename

SET @DestPath = '/folder' -- Destination path. Blank for root directory.

SET @FTPMode = 'binary' -- ascii, binary or blank for default.

DECLARE @cmd varchar(1000)

DECLARE @workfile 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'

-- 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

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

IF LEN(@DestPath) > 0

BEGIN

select @cmd = 'echo ' + 'cd ' + @DestPath + ' >> ' + @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

select id, ouputtmp = s from #a

-- Clean up.

drop table #a

select @cmd = 'del ' + @tempdir + @workfile

EXEC master..xp_cmdshell @cmd