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
 General SQL Server Forums
 New to SQL Server Programming
 FTP cmd script to Pasive

Author  Topic 

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2012-05-21 : 06:52:23
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

420 Posts

Posted - 2013-04-03 : 03:41:55
why output will be NULL when i try to run it?
Go to Top of Page
   

- Advertisement -