What Is Best Practice For FTP From A SQL Server 2005 Stored Procedure?

- 1 answer

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'

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.