Unanswered: xp_cmdshell and ftp
When xp_cmdshell interacts with FTP it doesn't log the FTP return codes.
I found Nigel Rivett's T-SQL FTP scripts http://www.nigelrivett.net/ and concatenated them together with some modifications so I could have a process that actually errors when an FTP fails.
The process FTP's a file, disconnects, and then reconnects to do a dir and then select from the returned listing to see if the file name exists.
I wanted to review the initial log from the put and check for a valid FTP return code of 250 that says "Transfer completed successfully"
A normal FTP log shows:
ftp> put foo 'mainframe.node.structure.foo'
200 Port request OK.
125 Storing data set MAINFRAME.NODE.STRUCTURE.FOO
250 Transfer completed successfully.
ftp: 7972888 bytes sent in 99.63Seconds 80.03Kbytes/sec.
221 Quit command received. Goodbye.
xp_cmdshell log shows (with FTP debug on) shows:
7 put foo 'mainframe.node.structure.foo'
8 PORT 10,99,11,16,7,30
9 ---> STOR MAINFRAME.NODE.STRUCTURE.FOO
10 ---> quit
As you can see, the return codes and some other messages are missing.
I've messed around with various options and can't seem to get any return codes in the log at all.
Sr. Business Application Analyst