I found the problem. Removing the quotes from around the path to WinSCP solved it. In other words, if I change this:

EXEC xp_cmdshell '"C:\Progra~2\WinSCP\WinSCP.com" /command "open sftp://login:password@sftp.someserver.com:22" exit'

to this:

EXEC xp_cmdshell 'C:\Progra~2\WinSCP\WinSCP.com /command "open sftp://login:password@sftp.someserver.com:22" exit'

I no longer get the "Directory is not valid" error. It's very weird that this error only occurs when I use xp_cmdshell and also use the Open command.

I'm using WinSCP 4.3.9, build 1817. I'm trying to automate an upload. I have the WinSCP.com command line written, and it works when executed from a command window, but it doesn't work when executed from within SQL Server, using xp_cmdshell. It gives the error "The directory name is invalid". However, not all WinSCP command lines cause that error; only those with an open command.

To be more specific, if I execute the following from a command window (i.e., a "DOS" window):

"C:\Progra~2\WinSCP\WinSCP.com" /command "open sftp://login:password@sftp.someserver.com:22" exit'

it works. But if I execute the following from SQL Server, I get the error "The directory name is invalid."

EXEC xp_cmdshell '"C:\Progra~2\WinSCP\WinSCP.com" /command "open sftp://login:password@sftp.someserver.com:22" exit'

xp_cmdshell is a SQL Server command that executes an external process. The problem isn't related to xp_cmdshell per se though, because if I execute the following, I don't get an error:

EXEC xp_cmdshell '"C:\Progra~2\WinSCP\WinSCP.com" /command session exit'

Instead, I get the message "No session", as I would expect.

So I'm pretty sure the message "The directory name is invalid" is coming from WinSCP, not SQL Server, and that it is in some way related to WinSCP's Open command. Somehow, WinSCP's Open command is behaving differently when called from SQL Server rather than by me. I should mention that when xp_cmdshell executes a command, it executes it under a different account (not mine). So this may be a permissions problem. However, the account SQL Server uses is an administrator on this machine.

Can anyone help?

Thanks.