FTP Excel file to web site - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

FTP Excel file to web site

Author

Topic

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-16 : 11:07:48

Anybody knows how to FTP Excel file to the web site? I guess it's about DTS issues.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-08-16 : 11:10:38

HTH..
http://www.sqlteam.com/searchresults.asp?SearchTerms=FTP

- Sekar

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-16 : 12:35:46

http://www.nigelrivett.net/s_ftp_PutFile.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-17 : 09:50:46

I looked at http://www.nigelrivett.net/s_ftp_PutFile.html.
What is @workdir and @workfilename? What is the useage of this two?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-17 : 18:50:26

@workdir is a directory on the server for a temp file to hold the ftp commands.
@workfilename is the name of that temp file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-18 : 10:53:59

Thanks,nr,

I try that sp but still getting problems.The following is my code and result.

Declare @cmd varchar(1000)

select @cmd = 'echo ' + 'open ' + 'FTPADDRESS'
+ ' > ' + 'C:\FTPCommand' + 'ftpcmd.txt'
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'FTPUSERNAME'
+ ' >> ' + 'C:\FTPCommand' + 'ftpcmd.txt'
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' +'FTPPASSWORD'
+ ' >> ' + 'C:\FTPCommand' + 'ftpcmd.txt'
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + 'C:\' + 'Allot.txt' + ' ' + '/data/' +' ' + 'form_Agent.txt'
+ ' >> ' + 'C:\FTPCommand' + 'ftpcmd.txt'
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + 'C:\FTPCommand' + 'ftpcmd.txt'
exec master..xp_cmdshell @cmd

select @cmd = 'ftp -s:' + 'C:\FTPCommand' + 'ftpcmd.txt'

create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd

select id, ouputtmp = s from #a

The result I got from #a:

1 User (FTPADDRESS:(none)): open FTPADDRESS
2 NULL
3 NULL
4 User FTPUSERNAME cannot log in.
5 Login failed.
6 Invalid command.
7
8 put C:\Allot.txt data form_Agent.txt
9 Please login with USER and PASS.
10 quit
11 NULL

My questions are:
1. the FTPUserName is the user name I use to log in to FTP Server.Why it's failed? Is "Invalid command" caused by "Login Failed?"

2. Is @workdir the directory that hold ftpcommand.exe file? Why do we need "ftpcmd.txt"? What's that for? What I need is to transfer "C:\Allot.txt" to "date/form_Agent.txt" which locates on the FTP server.

3. By the way, can I use sp to transfer .xls file?

Thank you!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-18 : 12:49:02

What this does is create a text file of the ftp commands then runs the commands line ftp.exe with that file.
If you look in @workdir you should see ftpcmd.txt which holds the commands.
You can run ftp.exe and type in those commnds to see what is wrong.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-18 : 16:21:59

nr,

Would you look at my code above? It doesn't even create ftpcmd.txt file.

Thank you!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-18 : 17:17:33

' > ' + 'C:\FTPCommand' + 'ftpcmd.txt'
this will create the file
C:\FTPCommandftpcmd.txt
you probably need
' > ' + 'C:\FTPCommand\' + 'ftpcmd.txt'

That's not the problem but it shows you where to look for the file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-19 : 09:42:33

nr,

By using ' > ' + 'C:\FTPCommand\' + 'ftpcmd.txt', I get the following result:

OutPut:
The system cannot find the path specified.
NULL

Result from #a:

1 Error opening script file C:\FTPCommand\ftpcmd.txt.
2
3 Transfers files to and from a computer running an FTP server service
4 (sometimes called a daemon). Ftp can be used interactively.
5
6 FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-w:windowsize] [-A] [host]
7
8 -v Suppresses display of remote server responses.
9 -n Suppresses auto-login upon initial connection.
10 -i Turns off interactive prompting during multiple file
11 transfers.
12 -d Enables debugging.
13 -g Disables filename globbing (see GLOB command).
14 -s:filename Specifies a text file containing FTP commands; the 15 commands will automatically run after FTP starts.
16 -a Use any local interface when binding data connection.
17 -A login as anonymous.
18 -w:buffersize Overrides the default transfer buffer size of 4096. 19 host Specifies the host name or IP address of the remote 20 host to connect to.
21
22 Notes:
23 - mget and mput commands take y/n/q for yes/no/quit.
24 - Use Control-C to abort commands.
25 NULL

Would you tell me what's wrong? Thank you very much.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-20 : 12:08:48

Have you created the directory C:\FTPCommand\, does the sql server service have permission on it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-20 : 15:46:38

nr,

I did create directory C:\FTPCommand. What does sql server service permission mean?

Thank you!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-20 : 15:57:09

try
exec master..xp_cmdshell 'type C:\FTPCommand\ftpcmd.txt'

if that works try
exec master..xp_cmdshell 'ftp -s:C:\FTPCommand\ftpcmd.txt'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-23 : 08:32:34

nr,

I tried
exec master..xp_cmdshell 'type C:\FTPCommand\ftpcmd.txt'

This is result:

The system cannot find the path specified.
NULL

It seems it doesn't create file.How does this happen?
Thank you!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-23 : 08:37:59

Did you change the code to
' > ' + 'C:\FTPCommand\' + 'ftpcmd.txt'
With the extra backslash before 'ftpcmd.txt'?

try
exec master..xp_cmdshell 'dir C:\FTPCommand\'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-08-23 : 10:55:54

nr,

Yes, I did change the code to:
'> ' + 'C:\FTPCommand\' + 'ftpcmd.txt'

I tried
exec master..xp_cmdshell 'ftp -s:C:\FTPCommand\ftpcmd.txt'

This is the result:

The system cannot find the path specified.
NULL

What's wrong? Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources