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