execute PSFTP command line with XP_CMDSHELL in TSQL

[DennBen]

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'm 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'm 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,

[Erland Sommarskog]

DennBen (dben...@hotmail.com) writes:
> 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'm 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.

I would guess that it's waiting for user input. I hope this is a
command-line utility? If it's try to open a window of any sort, it's
not going to work out well at all.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Andy Jarman]

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D3A2611...@127.0.0.1...

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

[alextorres]

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

[Dennis]

Yes I did get this resolved. It is kind of an ugly work around. The error is because you are getting prompted the first time you run SFTP from that MSSQL account, so you have to force in a "y" response.

To do this I created a text file named "yes.txt" and inside of it i typed the word "yes".

Then in my batch script i reference it to answer the prompt like so:

psftp -P 23 192.168.1.251 -l myUserRed -pw mypasswordRed -b E:\Data\Test\Test2\ftp_commands\ftpScriptThatTransfersfiles.txt < E:\Data\Test\Test2\BCP\yes.txt

exit

Alex Torres wrote:

Resolved?
05-May-10

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.

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice

BOOK REVIEW: Effective C#, Second Edition [Addison Wesley]
http://www.eggheadcafe.com/tutorials/aspnet/b2f8766d-a4c1-4d5a-97af-c38852b3b455/book-review-effective-c.aspx

[pjmah...@gmail.com]

Wonder if you could echo yes and pipe it into the command?

ECHO yes | psftp -P 23 192.168.1.251 -l myUserRed -pw mypasswordRed -b E:\Data\Test\Test2\ftp_commands\ftpScriptThatTransfersfiles.txt