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,

DennBen (dbenedett (AT) hotmail (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

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/...er6.html#6.1.5 for
usage.


Andy

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/...er6.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...or-for-ne.aspx

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\ftpScriptThatTrans fersfiles.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...fective-c.aspx