Problems getting return codes from xp_cmdshell

[leescriven]

Hi,

Thi is my first post on this forum, and I'm hoping that there is a guru out there who can help me with an annoying problem I am having.

I have written a Stored Procedure that relies heavily on dynamic SQL to export a series of table/views to CSV format, then zip the files up, then it creates an series of ftp commands to put the newly created zip file onto the relevent ftp server. The list of files and destination server are held in a control table, and are different depending on the parameters passed.

There is a batch file that is called using the xp_cmdshell that executes the list of commands to put the file. The batch file is supposed to redirect the ftp output to a text file so that I can parse it, looking for the command successful following the put command.

This work fine when I start a cmd prompt manually, I can see the whole session log in the text file. However, when I execute the same batch file from xp_cmdshell, all that is in the session log, is the commands sent to the server, not the responses.

This is the ftp command list used held in a text file called Macro.ftp

open x.x.x.x
username
password
cd folder1
bin
put D:\FTP\folder1\file_20070628162831.zip
quit

This is the batch file that is executed

@echo off
SET STATUS_FILE=D:\FTP\folder1\ftpStatusFile.txt
SET SUCCESS_STATUS=200
SET FAILURE_STATUS=0
:Clear the existing Status file ***
IF EXIST %STATUS_FILE% DEL %STATUS_FILE%
:Try to ftp the file and log the success of the transfer ***
c:\windows\system32\ftp.exe -s:D:\FTP\folder\Macro.ftp > %STATUS_FILE%
:Check the success of the transfer ***
FINDSTR /C:"200 PORT command successful." %STATUS_FILE%
IF %ERRORLEVEL%==1 SET RETURN_CODE=%FAILURE_STATUS%
IF %ERRORLEVEL%==0 SET RETURN_CODE=%SUCCESS_STATUS%
:Exit with the correct exit code.
echo %RETURN_CODE%
EXIT %RETURN_CODE%

This is the output if I run the code directly from a cmd window.

ftp> Connected to x.x.x.x.

open x.x.x.x
220-Microsoft FTP Service
220 FTP Service
User (x.x.x.x:(none)):
331 Password required for username.

230-Welcome
230 User username logged in.
ftp> ftp>
cd folder1
250 CWD command successful.
ftp> bin
200 Type set to I.
ftp> put D:\FTP\folder1\file_20070628162831.zip
200 PORT command successful.
150 Opening BINARY mode data connection for file_20070628162831.zip.
226 Transfer complete.
ftp: 11692 bytes sent in 0.19Seconds 62.52Kbytes/sec.

ftp> quit
221

And this is the output when I call it from xp_cmdshell

User (x.x.x.x:(none)): open x.x.x.x



cd folder
bin
put D:\FTP\folder1\file_20070628162831.zip
quit

As you can see, I was expecting to be able to parse the log file and find the line 226 Transfer complete. to denote a successful file transfer, then continue the SP sending an email notification of success or failure. But as I cannot get the session log to work, I always report failure.

Does anyone have any idea how I can get round this issue?

Regards
Lee

[Motoma]

Hi,

Thi is my first post on this forum, and I'm hoping that there is a guru out there who can help me with an annoying problem I am having.

I have written a Stored Procedure that relies heavily on dynamic SQL to export a series of table/views to CSV format, then zip the files up, then it creates an series of ftp commands to put the newly created zip file onto the relevent ftp server. The list of files and destination server are held in a control table, and are different depending on the parameters passed.

There is a batch file that is called using the xp_cmdshell that executes the list of commands to put the file. The batch file is supposed to redirect the ftp output to a text file so that I can parse it, looking for the command successful following the put command.

This work fine when I start a cmd prompt manually, I can see the whole session log in the text file. However, when I execute the same batch file from xp_cmdshell, all that is in the session log, is the commands sent to the server, not the responses.

This is the ftp command list used held in a text file called Macro.ftp

open x.x.x.x
username
password
cd folder1
bin
put D:\FTP\folder1\file_20070628162831.zip
quit

This is the batch file that is executed

@echo off
SET STATUS_FILE=D:\FTP\folder1\ftpStatusFile.txt
SET SUCCESS_STATUS=200
SET FAILURE_STATUS=0
:Clear the existing Status file ***
IF EXIST %STATUS_FILE% DEL %STATUS_FILE%
:Try to ftp the file and log the success of the transfer ***
c:\windows\system32\ftp.exe -s:D:\FTP\folder\Macro.ftp > %STATUS_FILE%
:Check the success of the transfer ***
FINDSTR /C:"200 PORT command successful." %STATUS_FILE%
IF %ERRORLEVEL%==1 SET RETURN_CODE=%FAILURE_STATUS%
IF %ERRORLEVEL%==0 SET RETURN_CODE=%SUCCESS_STATUS%
:Exit with the correct exit code.
echo %RETURN_CODE%
EXIT %RETURN_CODE%

This is the output if I run the code directly from a cmd window.

ftp> Connected to x.x.x.x.

open x.x.x.x
220-Microsoft FTP Service
220 FTP Service
User (x.x.x.x:(none)):
331 Password required for username.

230-Welcome
230 User username logged in.
ftp> ftp>
cd folder1
250 CWD command successful.
ftp> bin
200 Type set to I.
ftp> put D:\FTP\folder1\file_20070628162831.zip
200 PORT command successful.
150 Opening BINARY mode data connection for file_20070628162831.zip.
226 Transfer complete.
ftp: 11692 bytes sent in 0.19Seconds 62.52Kbytes/sec.

ftp> quit
221

And this is the output when I call it from xp_cmdshell

User (x.x.x.x:(none)): open x.x.x.x



cd folder
bin
put D:\FTP\folder1\file_20070628162831.zip
quit

As you can see, I was expecting to be able to parse the log file and find the line 226 Transfer complete. to denote a successful file transfer, then continue the SP sending an email notification of success or failure. But as I cannot get the session log to work, I always report failure.

Does anyone have any idea how I can get round this issue?

Regards
Lee

I think you may only receive error messages. Try executing a couple of things that will produce errors, and you will probably receive those with xp_cmdshell.