SQL Server‎ > ‎

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



Comments