[SQL] Stored Procedure To FTP Files From SQL Server

sql 0 comments suggest edit

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.

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.

Comments

avatar

16 responses

  1. Avatar for Barry Dorrans
    Barry Dorrans April 21st, 2006

    And don't forget, by default, xp_cmdshell is disabled in SQL 2005.

  2. Avatar for shweta
    shweta August 3rd, 2006

    do you know how to do a secured ftp from SQL

  3. Avatar for Raz
    Raz January 4th, 2007

    Can you use this to transfer data from different servers, for example I want to ftp *.dat files to my sql server database from another secure server. Can I use this code to locate these files.
    Thanks

  4. Avatar for colin
    colin March 30th, 2007

    Hello there,
    I tried to execute this sp from a DTS package task and keep getting the error "Invalid object name '#a'".
    Any clue as to what I can do t fix or why it doesn't see #a- its part of the sp as a temp table, right?
    Thanks in advance

  5. Avatar for Haacked
    Haacked March 30th, 2007

    Weird. If you look at the query, there's the following snippet.

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

    The first line creates the temp table, the second line inserts into it. Not sure why that wouldn't work as part of a DTS package.

  6. Avatar for Art
    Art January 7th, 2008

    do you know how to do ,or if exists, a stored procedure to SQL Server "table" From FTP Files? Thanks

  7. Avatar for Oscar Ågren
    Oscar Ågren March 27th, 2008

    You said that you made some modifications to the code. But you didn't post the new source code?
    Cheers

  8. Avatar for Frederick
    Frederick September 4th, 2008

    Someone or the author can update this site?
    Seems like not moderated....

  9. Avatar for Thomas Mucha
    Thomas Mucha November 23rd, 2008

    This was very helpful. Previously we had been generating an ftp script file using vbs (not my strong point) and I couldn't easily pass date parameters to it. Now it's solved!

  10. Avatar for hhgregg
    hhgregg February 1st, 2011

    Thanks for sharing this, Phil! We use to accomplish this using a VB Script where I couldn’t pass date parameters. Is it possible to do a secured FTP from the stored procedure? Did you post the new source code that combines the Stored Procedure FTP with the Time of Day generator?

  11. Avatar for recruiting software
    recruiting software October 5th, 2011

    Good call, Barry. I kept forgetting about xp_cmdshell being disabled in 2005 and it almost drove me insane.
    I look forward to seeing what you're using this code for. I like it, but for the life of me, I can't figure out what it's good for! Ha.
    -Jameson

  12. Avatar for venkat
    venkat January 13th, 2012

    Can I ftp files(.dat files) from a sql server to a unix server.
    If so any suggestions how it can be implemented.

  13. Avatar for Marcel
    Marcel April 21st, 2012

    I keep getting error on authentication, while when using the same credentials in manually FTP there is no problem. Could there be a way to put the credentials in one single command?

  14. Avatar for Graham
    Graham September 5th, 2012

    Here's a fix that might help some folks.
    I was getting a STOR 550 error when running the stored proc. It was when it was attempting to put the file on the FTP server.
    I figured out that the /@ftpPath/@FTPFileName was not doing the cd (change directory) properly. So I added a few lines and modified one.
    /*** all code above this part is perfect ***/
    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
    /*** Added lines: ***/
    select@cmd = 'echo cd '+ @FTPPath
    + '>> ' + @workdir + @workfilename
    exec master..xp_cmdshell @cmd
    /*** End of added lines ***/
    /*** Modified line.. deleted the "+ @FTPPath" from this append ***\
    select@cmd = 'echo '+ 'put ' + @SourcePath + @SourceFile + ' ' + @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
    -- All code below this works perfect
    /*
    Just a heads up the directory on the server is just the name of the directory. eg. public not /public/ . So the cd command is cd public.
    All the rest of the code worked like a hot damn. Thanks Nigel you write some fine code. I'm not worthy.
    */

  15. Avatar for Scott
    Scott December 15th, 2015

    This breaks if it is called by two different sprocs at the same time, since it uses a work file to build the ftp commands. Just a word of caution.

  16. Avatar for Pradeepa S
    Pradeepa S October 24th, 2017

    I am not able get the complete code and I want the complete code for generate a file and copy that file into FTP Server. i am request here to post the complete code(FtpPutFile) with updated one.