Capturing BAT file ERRORLEVEL from xp_cmdshell in stored procedure

• richardbartonbrown

  SSC Veteran

  Points: 241

  More actions

  August 25, 2009 at 8:02 am

  #73315

  This is an minor FYI, just to get this info where it can be searched and googled, and save somebody some time.

  I'm testing xp_cmdshell to run some BAT scripts from a stored procedure in SQL Server 2000. I wanted to see the BAT file exit status to drive the stored procedure logic. However the BAT file exit code was not returned to the stored procedure (in Windows 9.x the status is never returned, but I'm using Windows Server 2003).

  Here are example lines from the BAT file, where even if the osql command failed and the BAT file echo'd an ERRORLEVEL = 1, the xp_cmdshell return value was zero. (don't ask me why I'm doing SQL in a BAT called from a stored procedure...trust me!):

  --------------------------------

  osql -S serverX -d databaseX -U userX -P passwordX -b -Q "INSERT INTO table_flag (key_value) VALUES (1)"

  ECHO Errorlevel is %ERRORLEVEL%

  IF %ERRORLEVEL% NEQ 0 GOTO End

  ...

  :End

  EXIT /B %ERRORLEVEL%

  --------------------------------

  I searched the web high and low, and found a comment on a SQL Anywhere blog described as "in the department of Waving A Dead Chicken Over The Keyboard..." (the page is at http://www.itags.org/database/359384/). The poster suggested trying a CALL before the BAT file in xp_cmdshell. It worked, and the BAT ERRORLEVEL is now returned. The example xp_cmdshell code is:

  --------------------------------

  @error_save INTEGER,

  @stmt VARCHAR(8000),

  ...

  SET @stmt = 'CALL \\serverX\E$\directoryX\BatchFile.bat'

  EXEC @error_save = master.dbo.xp_cmdshell @stmt, 'NO_OUTPUT'

  PRINT '@error_save after cmdshell='+Convert(VARCHAR,@error_save)+', 1=fail , 0=ran successfully.'

  IF @error_save <> 0 BEGIN

  ...

  --------------------------------