How to execute Winscp from SQL Server 2000 xp_cmdshell?

WinSCP sessions can be stored when run from its UI or command prompt.
However, it fails when executed from query analyzer due to the fact that it can find or remember any stored sessions. Ex T-SQL script:

declare @cmd varchar(1000)
set @cmd = 'CMD /C ""c:\program files\winscp\winscp.com" /script="D:\GEMS_EXPORT\BVHE\ftp_command.txt""'
exec master..xp_cmdshell @cmd

Results:
batch on
confirm off
No session.
Searching for host...
Connecting to host...
Authenticating...
The server's host key was not found in the cache. You have no guarantee that the server is the computer you think it is.
The server's rsa2 key fingerprint is:
ssh-rsa 1024 cd:7a:01:2b:5b:19:cc:97:8b:65:c5:c4:f0:6d:72:f6
If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
Continue connecting and add host key to the cache?
(Y)es, (N)o, (C)ancel: Cancel
Host key wasn't verified!
Authentication failed.
transfer binary
No session.
D:\GEMS_EXPORT\BVHE
No session.
No session.
NULL

As you can see, I cannot answer for the prompt of adding the host key to the cache since it is executed via xp_cmdshell. Do you know of some workaround?

I think the solution to my problem is using the new feature "/hostkey" which is still pending. Any idea, when the next release would be?

It's funny that I have been replying to my own post :lol:

Anyhow, the solution is to configure the storage options (e.g. ini instead of registry). The system would then reference the ini file and remember the host key. Thus, resolves the xp_cmdshell issue. Hope this helps the needy :mrgreen:

Thanks for the tip! Switching it to store all settings in an ini file rather than in the registry worked great!

andy0789 wrote:

Thanks for the tip! Switching it to store all settings in an ini file rather than in the registry worked great!

can u explain indetail to store in ini file, becoz i have face the same issue. still not solved

Arun wrote:

can u explain indetail to store in ini file, becoz i have face the same issue. still not solved

Please read documentation. If that does not help, come back.

Hi
We execute the below line from command prompt
>E:\winscp382.exe /console /script=E:\FtpGetFile.txt


The FtpGetFile.txt contain below script
option batch on
option confirm off
open username:password@xx.xxx.com
get Upload.txt e:\Upload.txt
close
exit



it throws authentication failure error
hostkey etc...
can u explain where to put hostkey value
or using ini file?javascript:emoticon(':oops:')

can u explain where to put hostkey value
or using ini file?

Please read FAQ. If that does not help, come back.

the first time you run it, use ' echo Y | {....your command....} ' this will hit "Y"


:twisted:

halx wrote:

the first time you run it, use ' echo Y | {....your command....} ' this will hit "Y"

That's indeed the worst solution.