INF: Using Return Codes with xp_cmdshell Stored Procedure

ID: Q184039

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5

SUMMARY

This article describes the behavior that you will observe when you use return codes with the xp_cmdshell stored procedure on different versions of Microsoft SQL Server.

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'

Version 4.21 and 6.0 Behavior

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.

Version 6.5 Behavior

In version 6.5, 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.

Keywords : SSrvStProc SSrvTran_SQL
Version : WinNT:4.2x,6.0,6.5
Platform : winnt
Issue type : kbinfo