SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 FTP cmd script to Pasive
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 05/21/2012 :  06:52:23  Show Profile  Reply with Quote
I am using the following script below to download a file from an FTP site. I am trying to change it to work in pasive mode. I have commented out what I was trying.

I would be very glad it someone to help me adjust the script

ALTER procedure [dbo].[UDEF_KD_s_ftp_GetFile]
-- FTP_MGET.sql (Written by John Buoro)
-- Transfer multiple files from an FTP server using MGET.

@FTPServer varchar(128),
@FTPUser varchar(128),
@FTPPwd varchar(128),
@SourcePath varchar(128),
@SourceFiles varchar(128),
@DestPath varchar(128),
@FTPMode varchar(10)

as
-- FTP attributes.
/*SET @FTPServer = 'ftp.easyorder.eu'
SET @FTPUser = 'hbsgroup'
SET @FTPPwd = 'H85gtop'
SET @SourcePath = '/in/' -- Source path. Blank for root directory.
SET @SourceFiles = '44272_catalogue.csv'
SET @DestPath = 'D:\WebFiles\' -- Destination path.
SET @FTPMode = 'binary' -- ascii, binary or blank for default.*/

DECLARE @cmd varchar(1000)
DECLARE @workfile varchar(128)
DECLARE @nowstr varchar(25)

-- Get the %TEMP% environment variable.
DECLARE @tempdir varchar(128)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
SET @tempdir = (SELECT top 1 info FROM #tempvartable)
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'
DROP TABLE #tempvartable

-- Generate @workfile
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'

-- Deal with special chars for echo commands.
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPwd = replace(replace(replace(@FTPPwd, '|', '^|'),'<','^<'),'>','^>')
select @SourcePath = replace(replace(replace(@SourcePath, '|', '^|'),'<','^<'),'>','^>')
IF RIGHT(@DestPath, 1) = '\' SET @DestPath = LEFT(@DestPath, LEN(@DestPath)-1)

-- Build the FTP script file.


select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile




EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile



EXEC master..xp_cmdshell @cmd
IF LEN(@FTPMode) > 0




BEGIN
select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
END

--select @cmd = 'echo ' + 'quote pasv' -- literal
--+ ' >> ' + @tempdir + @workfile
--exec master..xp_cmdshell @cmd




select @cmd = 'echo ' + 'lcd ' + @DestPath + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@SourcePath) > 0
BEGIN
select @cmd = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
END



select @cmd = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

-- Execute the FTP command via script file.
select @cmd = 'ftp -s:' + @tempdir + @workfile
create table #a (id int identity(1,1), s varchar(1000))
insert #a
EXEC master..xp_cmdshell @cmd
select id, ouputtmp = s from #a

-- Clean up.
drop table #a
select @cmd = 'del ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd


GO

peace
Constraint Violating Yak Guru

391 Posts

Posted - 04/03/2013 :  03:41:55  Show Profile  Reply with Quote
why output will be NULL when i try to run it?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000