Tsql FTP Connection Syntax

This might be helpful

http://www.sqlteam.com/article/using-ftp-in-transact-sql

some what helpful i can do this already this is downloading a file.. I need to do an ls on the root directory so I can get a list of files on that directory

I may be missing something but... root directory?
Is the root not that of the currently logged in user when it comes to FTP set-ups

Well the root directory of user i am logging in as correct..
I need to duplicate the same logic above but the select should return the files of the FTP directory  

http://www.nigelrivett.net/FTP/s_ftp_GetDir.html

Just left office .. Did read code look it does what I need give me some time to test. Thank you!!

this returns

open sitename.com
UserName
password
ls /
quit
NULL

I have created this proc

Alter procedure dbo.s_ftp_GetDir
@FTPServer      varchar(128) ,
@FTPUser      varchar(128) ,
@FTPPWD            varchar(128) ,
@FTPPath      varchar(128) ,
@workdir      varchar(128)
as

declare      @cmd varchar(1000)
declare @workfilename varchar(128)
      
      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, '|', '^|'),'<','^<'),'>','^>')


Print @FTPServer
Print @FTPUser
Print @FTPPWD
Print @FTPPath

      create table #a (id int identity(1,1), s varchar(1000))

      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 '                              + 'ls ' + @FTPPath
                  + ' >> ' + @workdir + @workfilename
      insert #a
      exec master..xp_cmdshell @cmd
      select      @cmd = 'echo '                              + 'quit'
                  + ' >> ' + @workdir + @workfilename
      exec master..xp_cmdshell @cmd
      
      select @cmd = 'ftp -s:' + @workdir + @workfilename
      
      print @cmd
      create table #b (id int identity(1,1), s varchar(1000))
      insert #b
      exec master..xp_cmdshell @cmd
      
      select id, s from #a

      select id, s from #b

its generating this file called ftpcmd.txt

open securefiletransfer.vendor.com
UerNAme
PaSSword
Dir /
quit

then i run xp_commandshell

on that file and it just displays the content of the file that i just wrote above

The path on the FTP server is the root which is the one I need to look at

lcohan:

What I like about your code is that it is short and it does the same exact thing.. The select returns the content of my text connection file

declare @filename sysname
set @filename = 'E:\Vendor\Line\FilesFromSecureSFT\ftpcmd.txt'

Create table #tempfile (line varchar(8000))
    exec ('bulk insert #tempfile from "' + @filename + '"')
    select * from #tempfile

I run the command at the command line and I get this
C:\>ftp -s:E:\vendor\Line\FilesFromSecureSFT\ftpcmd.txt


result from command
Error opening script file E:\vendor\Line\FilesFromSecureSFT\ftpcmd.txt.

Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.

FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuf
fer] [-b:asyncbuffers] [-w:windowsize] [host]

  -v              Suppresses display of remote server responses.
  -n              Suppresses auto-login upon initial connection.
  -i              Turns off interactive prompting during multiple file
                  transfers.
  -d              Enables debugging.
  -g              Disables filename globbing (see GLOB command).
  -s:filename     Specifies a text file containing FTP commands; the
                  commands will automatically run after FTP starts.
  -a              Use any local interface when binding data connection.
  -A              login as anonymous.
  -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
  -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
  -b:async count  Overrides the default async count of 3
  -w:buffer size  Overrides the default transfer buffer size of 65535.
  host            Specifies the host name or IP address of the remote
                  host to connect to.

Notes:
  - mget and mput commands take y/n/q for yes/no/quit.
  - Use Control-C to abort commands.

not sure what you are trying to do with that command but I think you may have typed in the wrong switch:

-s:filename     Specifies a text file containing FTP commands; the
                  commands will automatically run after FTP starts.

if indeed ftpcmd.txt has all the FTP commands that need to be executed then make sure the path "E:\vendor\Line\FilesFromSecureSFT\" and the file are on the server where you run it. you may want to use double quotes if actual path on the SFTP server path has spaces in it.

we dont have the the FTP ports open

We use WinSCp and we dont use password files we used WinsCP sessions

Thats why  its not working

Ouch...well we can't really help you with that :)

i will leave open for a day or 2 else I will close.. It was great help.. Tho

May work but not in our environment since we we WinSCP sessions to do connections..

Thanks for the help tho

Just wanted to post my solution for future reference

DECLARE @CompletedFilePath varchar(2000)
Set @CompletedFilePath = 'Create or place File Name to be uploaded here'


if @upload = 1
BEGIN

DECLARE @UploadScriptFilePath AS VARCHAR(1000)  
DECLARE @UploadCMD AS VARCHAR(1000)  
DECLARE @WINSCPCMD AS VARCHAR(8000)  
DECLARE @OLEResult int  
DECLARE @FS int  
DECLARE @FileID int  
--DECLARE   
  
SET @UploadScriptFilePath = 'Place FIle to Upload here \sftpuploadScript.txt' --This is the script that connects not the file to be uploaded 
  
SET @UploadCMD = 'del ' + @UploadScriptFilePath --> Deletes and recreates the script generated by the tool  
PRINT @UploadCMD  
EXEC [MIA2-MSS-018].MASTER.dbo.XP_CMDSHELL @UploadCMD  
  
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT  
EXEC @OLEResult = sp_OAMethod @FS, 'CreateTextFile', @FileID OUT, @UploadScriptFilePath , 2, 0  
      SET @WINSCPCMD = 'option batch on'  + CHAR(13)+ CHAR(10)  
                              + 'open NAME_OF_WINSCP_Session_Here'  + CHAR(13)+ CHAR(10)  
                              + 'PUT ' + @CompletedFilePath + '' +CHAR(13)+ CHAR(10)    
                              + 'close'  + CHAR(13)+ CHAR(10)  
                              + 'exit'  + CHAR(13)+ CHAR(10)  
EXEC @OLEResult = sp_OAMethod @FileID, 'Write', Null, @WINSCPCMD  
  
--cLOSE fILE  
EXEC @OLEResult = sp_OAMethod @FS, 'Close', NULL  
EXEC @OLEResult = sp_OADestroy @FileID  
EXEC @OLEResult = sp_OADestroy @FS  

--This is the path to winSCP exectable that will run the script  
SET @UploadCMD = 'C:\PROGRA~1\WinSCP3\WinSCP3.exe /ini /script=' +  @UploadScriptFilePath   
PRINT @UploadCMD  
EXEC [ServerName].Master.dbo.xp_cmdshell @UploadCMD  

END

Select allOpen in new window