Batch file not executing psftp commands

Question

I am trying to upload a file from my local system to an FTP which is configured in Linux machine using T-SQL cmd. I have created a batch file with the commands to be executed which is working correctly in cmd where it is not working in T-SQL while calling the same batch file in SQL using xp_CMDShell. Please find below the commands I have used.

Batch file cmd:

cd C:\Program Files\PuTTY
psftp.exe
open FTPip
username
password
put D:\localpath\filename /home/destinationpath/filename
quit 

T-SQL:

EXEC master..xp_CMDShell 'D:\batchfilepath\batchfile.bat'

T-SQL response:

C:\Windows\system32>cd C:\Program Files\PuTTY NULL C:\Program Files\PuTTY>psftp.exe psftp: no hostname specified; use "open host.name" to connect psftp> quit NULL C:\Program Files\PuTTY>open ftpip 'open' is not recognized as an internal or external command, operable program or batch file. NULL C:\Program Files\PuTTY>username 'username' is not recognized as an internal or external command, operable program or batch file. NULL C:\Program Files\PuTTY>password 'password' is not recognized as an internal or external command, operable program or batch file. NULL C:\Program Files\PuTTY>quit
'quit' is not recognized as an internal or external command, operable program or batch file. NULL

Please let me know where I am missing.

Answer 1

Your are combining psftp commands and Windows commands into one file. That cannot work. The batch file stops on the call to the psftp and waits for it to finish. psftp on the contrary does not know that the batch file even exist, so it cannot read its commands from there.

Put your psftp commands (lines open though quit) into a separate file (upload.txt) and execute it with the following command:

"C:\Program Files\PuTTY\psftp.exe -b c:\path\to\upload.txt

(you can execute that command directly from T-SQL or put it to your batch file)

---

Note your batch file cannot work from cmd either, contrary to what you claim in your question. You have probably meant, that typing those lines in cmd manually works, what is something else.