Wednesday, January 02, 2013 3:46 PMWhat 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 188.8.131.52'
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.
Wednesday, January 02, 2013 4:07 PM
Basically: None, as when implemented as synchronous operation it may last in a long running transaction which depends on external systems. This is normally an unwanted situation.
Otherwise when it is really needed, I would use .NET CLR integeration and use a FTP procedure written in C#/VB.NET. But this also requires enhanced permissions. E.g. How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration and use an FtpWebRequest.
Thursday, January 03, 2013 12:44 AMModerator
I agree with Stefan that it's probably better to perform the FTP using an asynchronous technique. Instead of xp_cmdshell, you could launch an SSIS package asynchronously. This can be done with sp_start_job, the Service Broker External Activator or SSIS catalog procs (SQL Server 2012).
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Thursday, January 03, 2013 8:59 AMModerator
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.
TechNet Community Support
- Marked As Answer by Iric WenModerator Wednesday, January 09, 2013 9:25 AM