INFO: Using Return Codes with xp_cmdshell Stored Procedure

MORE INFORMATION

The "Microsoft SQL Server Transact-SQL Reference" includes an example of the xp_cmdshell extended stored procedure and how to use return codes from it in a batch to perform conditional execution. It is documented incorrectly, however.

Using the following modified example, you can expect different behavior Based on which version of Microsoft SQL Server you are using.

Example

   DECLARE @result int
   EXEC @result = xp_cmdshell "dir *.exe"
   /* The original result was evaluated against 1 */ 
   IF (@result = 0)
      PRINT 'Success'
   ELSE
      PRINT 'Failure'
				

Behavior in Version 4.21 and 6.0

The return code in xp_cmdshell in versions 4.21 and 6.0 is limited in that it returns a value of either 0 or 1 depending on whether the command was executed successfully. The return code does not provide the actual exit code of the command being called.

Behavior in Version 6.5 and Later

In version 6.5 and later, xp_cmdshell is improved. It returns the actual exit code from the command being executed. This provides a much more useful mechanism in using the return code inside a batch or stored procedure.