• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

how can i store ftp result in table

I'm running code as follows
Create table #temp_ftp_results (ftp_output varchar(255))

set @cmd = 'ftp -s:'+@batch_ftp+' '+@ftp_to_server

Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd

above code works fine but it doeanot stored FTP code in #temp_ftp_results Why?

how can i grab ftp result code in to table so i can search for each code and see if ftp fail.
Bharat Guru
Bharat Guru
1 Solution
Hi patel100,

We need more information.

How are you getting the FTP information to the SQL server?
I would assume you are using a DTS to read a text file, but I cannot be sure.

How are you sure that the FTP data is accessible by the SQL server?

Have you been able to access the data through other means in SQL?

Maybe answering some of these questions might help you figure out what is wrong.
Scott PletcherSenior DBACommented:
Don't know if this will help but BOL mentions NVARCHAR being returned from xp_cmdshell.  You might want to try changing return type from VARCHAR to NVARCHAR.
Bharat GuruAuthor Commented:
i'm actually seting
set @batch_ftp = "c:\myfolder\ftpbatch.bat"

ftpbatch.bat contains all ftp commands such as
put filename

everything works fine but follwing comadns doesnot put ftp series code in table it only puts discription wiout serial code.

Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Bharat GuruAuthor Commented:
This is actually command

Create table #temp_ftp_results (ftp_output NVARCHAR(255))

Set @cmd = "ftp -i -s:c:\temp\1_2003022.bat servername"
Insert #temp_ftp_results Exec master.dbo.xp_cmdshell

sample output

ftp_output                                                 -------------------
User (servername.website.com:(none)):
cd erase
put c:\temp\erase1.txt
put c:\temp\erase2.txt

But above table out put doeanot put any ftp series code like
530, 200, 530 ...
Hi patel100,

Have you considered if the INSERT is somehow considering the series code to be another data column (comma deliminated file?)?

Have tried doing this in the DTS interface to see if it is differentiating between the parts of the data?

I presume you want to see the verbose output of the FTP session (e.g. "226 Listing completed." when sending a "dir" command).  If so, then the problem is not in the SQL connection or the return datatype, but in the FTP commands.  

The syntax you want to use is "ftp -n -i -v -s:" and then the rest.  
   Using the -v switch depends on your settings; when interactive, the default is verbose, but -v disables that.  When I connected through SQL, -v enabled the verbose setting.  
   Using the -i switch disables interactive prompting.
   Using the -n switch disables autologin, allowing you to specify the login in the -s script file.

Also, before inserting into the table, just run the exec statements to make sure you're getting the results you need.

Hope this helps!
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now