Let’s be clear: I don’t recommend the use of xp_cmdshell as a general tool. It ought to be used when you have no alternatives, and you should carefully control access and what this can do. Opening a shell from SQL Server can be dangerous for your server.

That being said, I do think there are places that xp_cmdshell works great and if you need to use it, how do you tell if the commands you executed were successful?

For those of you that have worked in command lines, or remember DOS, there is a thing called an Errorlevel, that is returned by programs when they exit. For many of us that used to program in DOS, we always checked ERRORLEVEL when exiting a program, and we made sure that our program always returned a 0 if it exited without an error. That’s a standard that has been in place as long as I can remember working with computers.

How can we use this? If I execute this in a command window:

I get an instance of Notepad on my screen. When the CD.exe application completes, it sets the errorlevel to 0. Technically it returns a 0 to cmd.exe, which sets the errorlevel to that value.

What about this:

In this case, nothing happens. However if I switch the command to:

Then I get notepad again.

How can we use this in SQL Server? When xp_cmdshell exits, it returns the errorlevel from it’s shell. Let’s test it. First, enable xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Now execute

DECLARE @i INT
EXEC @i = xp_cmdshell 'dir'
SELECT @i

This returns the current directory (\Windows\System32 in this case) and a 0.

Now if we change to our other example:

DECLARE @i INT
EXEC @i = xp_cmdshell 'cd tim'
SELECT @i

We get these results

Since that’s not a valid path on my system, the DOS error is returned as a result, and the errorlevel is returned in the variable assignment.

You can use this to test and see if your commands execute before you do something, like try to parse a directory listing that isn’t there.