asked on

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.

Divaqs

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 Pletcher

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 Guru

ASKER

i'm actually seting
set @batch_ftp = "c:\myfolder\ftpbatch.bat"

ftpbatch.bat contains all ftp commands such as
userid
password
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

Bharat Guru

ASKER

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)):
NULL
NULL
cd erase
put c:\temp\erase1.txt
put c:\temp\erase2.txt
quit

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

ASKER CERTIFIED SOLUTION

Divaqs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KatanaCS

patel100,

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!
KatanaCS

CleanupPing

patel100:
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
EXPERTS:
Post your closing recommendations! No comment means you don't care.