Ideally would like to run something from a SQL query or SQL agent job to FTP upload a file to an external site, but cannot use xp_cmdshell.
Asked
Active
Viewed 2,969 times
0
-
1Like OPENROWSET? – S3S Mar 21 '17 at 18:17
-
There is an available SSIS Script that will handle FTP uploads etc. It can be seen [here](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2b7cf6b5-d16d-44cd-954d-68e9ba98ee91/ssis-script-task-that-ftps-files?forum=sqlintegrationservices). – Laughing Vergil Mar 21 '17 at 18:39
-
@Laughing Vergil I bookmarked that for the future - thanks! As of now we don't have SSIS available though.... – C-COOP Mar 21 '17 at 19:14
-
OPENROWSET doesn't need SSIS – S3S Mar 21 '17 at 19:15
-
@scsimon Do you have any sample code? No clue how that would work to export a file through FTP... – C-COOP Mar 21 '17 at 19:17
-
[Here](https://sql-programmers.com/tsql-openrowset-in-sql-server) is a good thread, and [here](http://www.theboreddba.com/Categories/miscellaneous/Using-OPENROWSET-instead-of-a-Linked-Server.aspx) is another. – S3S Mar 21 '17 at 19:19
-
I think that this question - http://stackoverflow.com/questions/37792082 - covers the issue completely. FTP is not accessible over OpenRowset. – Laughing Vergil Mar 21 '17 at 19:29
1 Answers
1
Yes. You need to split your work into two separate tasks:
- How to run executable or a batch program from within SQL Server without resorting to xp_cmdshell.
An example of how to do it can be found in:
https://www.mssqltips.com/sqlservertip/2014/replace-xpcmdshell-command-line-use-with-sql-server-agent/.
You should modify this example to suit your particular needs. Suggested stored procedure would:- run command passed as a parameter in created on-the-fly SQL job (indicate CmdExec subsystem)
- wait for SQL job completion (query msdb.dbo.sysjobactivity) or kill the job if predefined timeout value has been reached
- return results of job execution (query msdb.dbo.sysjobhistory)
- delete the job
Note: Full version of SQL Server required. If you are using express version, you would have to manually define a windows scheduled task.
- How to send a file via ftp using a batch program.
Please see:
How to ftp with a batch file?
