FTPGET Stored Procedure - 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

General SQL Server Forums

Script Library

FTPGET Stored Procedure

Author

Topic

nguyen
Starting Member

8 Posts

Posted - 2003-09-17 : 11:06:22

I modeled this stored procedure after several examples on the web. It uses the "FTP -s:mybatchfile.ext" option of DOS FTP. This command tells FTP to read the contents of a batch file. The stored procedure creates this file, executes FTP, and deletes the file. The file name needs to be unique to avoid collisions. Louis Nguyen.

----------------------------------------------------
CREATE procedure FTPGet
(
@FTPServer varchar(128), -- sever IP or alias
@FTPUser varchar(128), -- your username
@FTPPWD varchar(128), -- your password
@BATCHFile varchar(1000), -- name of batch file (unique)
@DestFile varchar(1000), -- name of file to create (destination)
@SourceFile varchar(128) -- name of file to get
)
as

declare @cmd varchar(1000)

select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @BATCHFile
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser + '>> ' + @BATCHFile
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD + '>> ' + @BATCHFile
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'get ' + @sourceFile + ' ' + @destfile + ' >> ' + @BATCHFile
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @BATCHFile
exec master..xp_cmdshell @cmd
select @cmd = 'ftp -s:' + @BATCHFile
exec master..xp_cmdshell @cmd
select @cmd = 'del ' + @BATCHFile
exec master..xp_cmdshell @cmd

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-18 : 04:39:17

Looks similar to my ftpput

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources