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