What is best practice for FTP from a SQL Server 2005 stored procedure?

Question

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.

Answer 1

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.