how can i store ftp result in table

Posted on 2003-02-24
Medium Priority
Last Modified: 2012-06-21
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.
Question by:Bharat Guru
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8009802
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.
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8009812
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.

Author Comment

by:Bharat Guru
ID: 8009993
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Author Comment

by:Bharat Guru
ID: 8010019
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 ...

Accepted Solution

Divaqs earned 75 total points
ID: 8010192
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?

Expert Comment

ID: 8021131

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!

Expert Comment

ID: 9276395
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question