Tuesday, September 04, 2007

FTP Put file using t-sql

I have to use a stored procedure which will send/Receive text file
from/to a server by using FTP.

So i googled on the net and i found a very interesting stored procedure written by Nigel Rivett
that will upload a file from the database.
But before using this stored procedure on SQL Server 2005,XP_CMDSHELL must be enabled,
because it's disabled by default on SQL Server 2005 for security reasons.
There are two ways to enable this option: Surface Area Configuration tool or sp_configure.
Enabling this option via Surface Area Configuration tool is rather straight forward.
Here is the instructions to enable xp_cmdshell in SQL Server 2005 via Surface Area Configuration tool:

1. Click the Start button.

2. Select All Programs.

3. Navigate to the Microsoft SQL Server 2005 folder.

4. On the flyout, mouseover Configuration Tools.

5. Select SQL Server Surface Area Configuration.

6. The SQL Server 2005 Surface Area Configuration window will appear.

7. At the bottom of the window, select Surface Area Configuration for Features.

8. In the left pane, under Database Engine, select xp_cmdshell.

9. Check the Enable xp_cmdshell checkbox.

10. Click OK.

11. Close the SQL Server 2005 Surface Area Configuration window.

Here is the script to enable xp_cmdshell in SQL Server 2005 via sp_configure:


EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE


-- FTP Put file using t-sql.
-- Author Nigel Rivett


This needs a work directory to create a FTP command file to execute.
In a multi-user system include the spid in the filename to make it unique for the connection.


if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ftp_PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ftp_PutFile]
GO


Create procedure s_ftp_putfile
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128)
as


declare @cmd varchar(1000)
declare @workfilename varchar(128)

/* usage:
exec s_ftp_putfile
@FTPServer = 'ftpserver' ,
@FTPUser = 'user' ,
@FTPPWD = 'password' ,
@FTPPath = 'path/' ,
@FTPFileName = 'test.txt' ,
@SourcePath = 'c:\' ,
@SourceFile = 'test.txt' ,
@workdir = 'c:\temp\'
*/

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '', '^'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '', '^'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '', '^'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '', '^'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'ftp -s:' + @workdir + @workfilename

create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd

select id, ouputtmp = s from #a

go

1 comment:

storyaboutherrr said...

i received NULL output.
is not working.