FTP file to a server

Question

FTP file to a server

Bakr Ben Ayad

Old Hand

Points: 325
More actions
March 20, 2007 at 9:30 am

#117971

Hi
I'm writing a stored procedure in sql server 2000. I get some data from a table and put it into a text file on the file server. I want to send this file to the ftp server using http://ftp. and at the end I need to check if the file has been transfered successfully to the ftp server.
here is my code but I still need to figure out how to check for a file if it has been transfered successfully or not. please help
insert ##tmpscript (Content)
select 'open ' + @site
union all
select @username
union all
select @password
union all
select 'put ' + @files_path + @file
union all
select 'quit'
SET @script_file = 'ftp_script.txt'
set @cmd = 'bcp "select Content from ##tmpscript order by RowNum" queryout ' + @files_path + @script_file + ' -T -c -S"' + @@servername + '"'
exec master..xp_cmdshell @cmd, no_output
Exporting files
SET @cmd = 'ftp -s:' + @files_path + @script_file
EXEC master..xp_cmdshell @cmd
Thanks for your help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i have this procedure which is designed to handle FTP to a site, with some minimal validation as to whether it sent or not...hope this helps: you'd want to expand the validation in step 3 to check to see if the results containded data implying the file uploaded successfully.

create proc up_FTPPushFile

@file_to_push varchar(255),

@ftp_to_server varchar(255),

@ftp_login varchar(255),

@ftp_pwd varchar(255)

as

Set Nocount On

--STEP 0

--Ensure we can find the file we want to send.

Create table #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

Insert #FileExists EXEC master.dbo.xp_fileexist @file_to_push

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)

BEGIN

Drop table #FileExists

RAISERROR ('File %s does not exist. FTP process aborted.', 16, 1, @file_to_push)

RETURN 1

END

--STEP 1

--Create xxx.bat batch file using bcp utility, file path/name is the same as @file_to_push

--batch file will hold 4 records:

--1) login

--2) password

--3) ftp command and file to push

--4) exit command

declare @sql varchar(255), @cmd varchar(255), @batch_ftp varchar(255), @ret int

set @sql = '"SELECT ftp_batch FROM ##temp_ftp_bat WHERE file_to_push = '''+ @file_to_push+'''"'

set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'

set @cmd = 'BCP '+ @sql +' queryout '+ @batch_ftp +' /T /c'

Create table ##temp_ftp_bat(ftp_batch varchar(255), file_to_push varchar(255))

Insert into ##temp_ftp_bat values (@ftp_login, @file_to_push)

Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)

Insert into ##temp_ftp_bat values ('put '+@file_to_push, @file_to_push)

Insert into ##temp_ftp_bat values ('bye', @file_to_push)

EXEC master.dbo.xp_cmdshell @cmd

Drop table ##temp_ftp_bat

--STEP 2

--Ensure we can find the batch file we just created.

Delete #FileExists

Insert #FileExists EXEC master.dbo.xp_fileexist @batch_ftp

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)

BEGIN

Drop table #FileExists

RAISERROR ('Unable to create FTP batch file %s. FTP process aborted.', 16, 1, @batch_ftp)

RETURN 1

END

Drop table #FileExists

--STEP 3

--Execute newly created .bat file, save results of execution

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

IF EXISTS (SELECT * FROM #temp_ftp_results WHERE (ftp_output like '%Login failed%' or ftp_output like '%Access is denied%'))

BEGIN

Drop table #temp_ftp_results

RAISERROR ('Unable to FTP file %s. Login failed or access denied. FTP process aborted.', 16, 1, @file_to_push)

RETURN 1

END

Drop table #temp_ftp_results

--STEP 3

--delete batch file

set @cmd = 'del '+@batch_ftp

EXEC master.dbo.xp_cmdshell @cmd

go

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

you can also ftp the file back from the remote server and compare it to the file you sent.

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 3

You can also use the DTS ftp task to transfer the file. In DTS, you can check to see if that step was successful. I hope that this helps. Thanks.

Chris

steve goluchowski SSC Veteran Points: 280 More actions · Answer 4

After your PUT line - "select 'put ' + @files_path + @file ", do an MDIR to a local file.

--> select 'mdir ' + @file + ' c:\temp\transfer.log'

And on the next line you'll need

--> select 'y'

immediately after as well. This "y" will answer the message to overwrite the transfer.log file.

Then check transfer.log in SQL like this

SET @cmdLine = 'find "' + @file + '" c:\temp\transfer.log'

EXECUTE @returnCode = master.dbo.xp_cmdShell @cmdLine, NO_OUTPUT

This just checks if the file exists on the FTP site. If you look at transfer.log you'll notice it also has the size of the file as it is on the FTP site. With that you could do another check.

~Steve