Stored proc xp_cmdshell hangs

[p27br]

hi there,

i have the following SP that connects to a remote FTP server and should download all files with the name preview.*.txt from the given folder.
I am using xp_cmdshell to execute the FTP commands but the files aren't getting downloaded to the folder c:\tmp
the QA just hangs.

CREATE procedure s_ftp_GetFiles
@FTPServer	varchar(128) ,
@FTPUser	varchar(128) ,
@FTPPWD	varchar(128) ,
@FTPPath	varchar(128) ,
@workdir	varchar(128)
as

declare	@cmd varchar(1000)
	
	-- deal with special characters for echo commands
	select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
	select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
	select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
	select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

	exec master..xp_cmdshell @cmd
	select @cmd = 'open ' + @FTPServer
	exec master..xp_cmdshell @cmd
             select @cmd = 'user ' + @FTPUser + ' ' + @FTPPWD
	exec master..xp_cmdshell @cmd
             select @cmd = 'ls ' + @FTPPath
	select @cmd = 'cd ' + @workdir
            select @cmd = 'toggle prompt'
	select @cmd = 'mget preview.*.txt'
	exec master..xp_cmdshell @cmd
GO

here is the command I enter in the QA window

exec s_ftp_GetDir '255.255.255.0','john','foo','/incoming/','c:\tmp'

 

[vongrunt]

Won't work that way - you must supply all FTP commands in one command shell. I'm not sure this is even possible without additional FTP scripting tools or SQL2k packages (File Transfer task).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[p27br]

well ideally i'd like to wrap it all up in a DTS package, using the FTP task.