This is another useful Sql Server Stored Procedure I found on the net written by Nigel Rivett.
The procedure uses the xp_cmdshell extended stored procedure to shell out an FTP command. You can use this procedure to ftp a file from one place to another. Of course, you will need to make sure that your command runs in the proper security context.
I made some very slight modifications in my own version of this procedure. I changed some of the parameters to be of type
nvarchar instead of varchar for my international friends. I also changed the name to suit my own naming conventions.
It takes in the following parameters.
| Parameter |
Data Type |
Description |
Example |
| @FTPServer |
varchar(128) |
The host name. |
ftp.example.com |
| @FTPUser |
nvarchar(128) |
The username for the FTP site. |
Haacked |
| @FTPPWD |
nvarchar(128) |
The password for the FTP site. |
Ha!_AsIfIWouldTellYou! |
| @FTPPath |
nvarchar(128) |
The subfolder within the FTP site to place the file. Make sure to use forward slashes and leave a trailing slash. |
/ |
| @FTPFileName |
nvarchar(128) |
The filename to write within FTP. Typically the same as the source file name. |
ImportantFile.zip |
| @SourcePath |
nvarchar(128) |
The path to the directory that contains the source file. Make sure to have a trailing slash. |
c:\projects\ |
| @SourceFile |
nvarchar(128) |
The source file to ftp. |
ImportantFile.zip |
| @workdir |
nvarchar(128) |
The working directory. This is where the stored proc will temporarily write a command file containing the FTP commands it will execute. |
c:\temp\ |
Here is an example of the usage.
exec FtpPutFile
@FTPServer = 'ftp.example.com' ,
@FTPUser = n'username' ,
@FTPPWD = n'password' ,
@FTPPath = n'/dir1/' ,
@FTPFileName = n'test2.txt' ,
@SourcePath = n'c:\vss\mywebsite\' ,
@SourceFile = n'MyFileName.html' ,
@workdir = n'c:\temp\'
I will soon combine this and my random time of day generator sql into a very useful stored procedure for you.