Tsql FTP Connection Syntax

I use this logic to retrieve the list of files on a local directory in

Declare @DirList table(entryID int Identity(1,1),fileName varchar(max), CreationTime dateTime default getDate())

insert into @DirList (fileName)
EXEC  master..xp_cmdshell 'dir /b'

Select * from @DirList

I need to do the same logic on the root of FTP site using TSQL code. We have WinSCP configured on the server

Any help is greatly appreciated
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperAuthor Commented:

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Leo TorresSQL DeveloperAuthor Commented:
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  
Leo TorresSQL DeveloperAuthor Commented:
Just left office .. Did read code look it does what I need give me some time to test. Thank you!!
Leo TorresSQL DeveloperAuthor Commented:
this returns

open sitename.com
ls /

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)

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 @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
lcohanDatabase AnalystCommented:
I usually use << ls [remote-directory] [local-file] >> command to generate the file list into a text file then get that text file somewhere you could read it in sql like below and then use the info from #tempfile to move on:

declare @filename sysname
set @filename = '\full_path\ftp_ls_filename.txt'

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:
its generating this file called ftpcmd.txt

open securefiletransfer.vendor.com
Dir /

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
Leo TorresSQL DeveloperAuthor Commented:

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
Leo TorresSQL DeveloperAuthor Commented:

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
  -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.

  - mget and mput commands take y/n/q for yes/no/quit.
  - Use Control-C to abort commands.
lcohanDatabase AnalystCommented:
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.
Leo TorresSQL DeveloperAuthor Commented:
we dont have the the FTP ports open

We use WinSCp and we dont use password files we used WinsCP sessions
Leo TorresSQL DeveloperAuthor Commented:
Thats why  its not working
lcohanDatabase AnalystCommented:
Ouch...well we can't really help you with that :)
Leo TorresSQL DeveloperAuthor Commented:
i will leave open for a day or 2 else I will close.. It was great help.. Tho
Leo TorresSQL DeveloperAuthor Commented:
May work but not in our environment since we we WinSCP sessions to do connections..

Thanks for the help tho
Leo TorresSQL DeveloperAuthor Commented:
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

DECLARE @UploadScriptFilePath AS VARCHAR(1000)  
DECLARE @OLEResult int  
DECLARE @FileID int  
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  
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  


Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.