What is the best method for executing FTP commands from a SQL Server stored procedure? we currently use something like this:
EXEC master..xp_cmdshell 'ftp -n -s:d:\ftp\ftpscript.xmt 172.1.1.1'
The problem is that the command seems to succeed even if the FTP ended in error. Also, the use of xp_cmdshell requires special permissions and may leave room for security issues.
If you're running SQL 2005 you could do this in a CLR integration assembly and use the FTP classes in the System.Net namespace to build a simple FTP client.
You'd benefit from being able to trap and handle exceptions and reduce the security risk of having to use xp_cmdshell.
Just some thoughts.
If you need to do FTP from within the database, then I would go with a .NET assembly as Kevin suggested. That would provide the most control over the process, plus you would be able to log meaningful error messages to a table for reporting.
Another option would be to write a command line app that read the database for commands to run. You could then define a scheduled task to call that command line app every minutes or whatever the polling period needed to be. That would be more secure than enabling CLR support in the database server.
Another possibility is to use DTS or Integration Services (DTS for SQL Server 7 or 2000, SSIS for 2005 or higher). Both are from Microsoft, included in the Sql Server installation (in Standard edition at least) and have an FTP task and are designed for import/export jobs from Sql Server.
Kev posted an example over here that implements his suggestion to "do this in a CLR integration assembly and use the FTP classes in the System.Net namespace to build a simple FTP client."