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
Searching for host...
Connecting to host...
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!
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?