From: Alex on
Did you ever get this resolved? I am having the same issue trying to post files to an SSH FTP server using psftp and XP_CMDSHELL.



DennBen wrote:

execute PSFTP command line with XP_CMDSHELL in TSQL
12-Mar-10

I am having trouble executing command line to transfer files via an
SFTP server from a call in T-SQL (version 2005) where putty is the
client application. The solution has to be able to work on SQLExpress
and needs to be backwards compatible to SQL 2000, so I am not sure if
SSIS is even a viable option for me.

The command executes perfectly in dos prompt, but in MSSQL it just
hangs on the connection command and never makes the connection.



THis is all I am running from MSSQL:
Declare @cmd nvarchar(2000), @dir nvarchar(200), @dbname
nvarchar(100), @sqlcmd nvarchar(2000)
SET @dir = 'E:\Data\Test\Test2'

SET @cmd = ltrim(rtrim(@dir)) + '\BCP\trySFTP3.bat -i';
exec xp_cmdshell @cmd

THis is what is in \BCP\trySFTP3.bat
E:
CD E:\Data\Test\Test2\clientFTPRoot\origin
psftp -P 23 192.168.1.33 -l demoRoboUser1 -pw 12345

bye


Any help would be GREATLY appreciated!

thanks,

Previous Posts In This Thread:

On Friday, March 12, 2010 8:22 AM
DennBen wrote:

execute PSFTP command line with XP_CMDSHELL in TSQL
I am having trouble executing command line to transfer files via an
SFTP server from a call in T-SQL (version 2005) where putty is the
client application. The solution has to be able to work on SQLExpress
and needs to be backwards compatible to SQL 2000, so I am not sure if
SSIS is even a viable option for me.

The command executes perfectly in dos prompt, but in MSSQL it just
hangs on the connection command and never makes the connection.



THis is all I am running from MSSQL:
Declare @cmd nvarchar(2000), @dir nvarchar(200), @dbname
nvarchar(100), @sqlcmd nvarchar(2000)
SET @dir = 'E:\Data\Test\Test2'

SET @cmd = ltrim(rtrim(@dir)) + '\BCP\trySFTP3.bat -i';
exec xp_cmdshell @cmd

THis is what is in \BCP\trySFTP3.bat
E:
CD E:\Data\Test\Test2\clientFTPRoot\origin
psftp -P 23 192.168.1.33 -l demoRoboUser1 -pw 12345

bye


Any help would be GREATLY appreciated!

thanks,

On Sunday, March 14, 2010 5:07 AM
Andy Jarman wrote:

As Erland mentioned, this will invoke psftp in 'interactive' mode, I
As Erland mentioned, this will invoke psftp in 'interactive' mode, I believe
you need the 'batch' functionality (-b). See
http://the.earth.li/~sgtatham/putty/0.52/htmldoc/Chapter6.html#6.1.5 for
usage.


Andy


Submitted via EggHeadCafe - Software Developer Portal of Choice
Crypto Obfuscator for .NET - Product Review
http://www.eggheadcafe.com/tutorials/aspnet/bf15c41b-6510-403e-9af8-f5fd987fafb1/crypto-obfuscator-for-ne.aspx