A person asked the following question:

I am running the following command:

EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

On the Results tab I get 2 lines Could not find a part of the path ‘serverdirectoryfilename’. NULL

How do I capture the first line in an error message? I tried using a Try Catch block with “SELECT @ErrorMessage = ERROR_MESSAGE()” and it doesn’t grab it.

The message is not coming from sys.messages. Where is this error message coming from then?

First of all that message comes from the Command Shell/DOS, not from SQL Server. There is a way to grab the message if you store the output in a table. The xp_cmdshell proc will return 1 if there is a failure and 0 if it executed succesfully

So if you were to execute the bogus command bla bla c: you would get the following output

‘bla’ is not recognized as an internal or external command,operable program or batch file.

If you did something like dir z: when you don’t have a z drive you would see the following

The system cannot find the path specified.

Now let’s look at some code by running a dir command on a drive that doesn’t exist, if you do have a z drive then change it to something that you don’t have

DECLARE @cmdline VARCHAR(500),
		@ReturnCode INT, 
		@ErrorMessage varchar(2000)

--Command to execute
SELECT @cmdline = 'dir z:'

-- Initialize variable
SELECT @ErrorMessage = ''

--Create temp table to hold result
CREATE TABLE #temp (SomeCol VARCHAR(500))

--dump result into temp table
INSERT #temp
EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

-- If we have an error populate variable
IF @ReturnCode <> 0
BEGIN
	SELECT @ErrorMessage = @ErrorMessage + SomeCol   
	FROM #temp
	WHERE SomeCol IS NOT NULL

	--Display error message and return code
	SELECT @ErrorMessage as ErrorMessage  ,@ReturnCode as ReturnCode

END
-- Look how 'green' we are
DROP TABLE #temp

After you run that you should see the following

  <th>
    ReturnCode
  </th>
</tr>

<tr>
  <td>
    The system cannot find the path specified.
  </td>
  
  <td>
    1
  </td>
</tr>

Change

SELECT @cmdline = 'dir z:'

to

SELECT @cmdline = 'bla bla z:'

Run the code again and now you should see the following output.

  <th>
    ReturnCode
  </th>
</tr>

<tr>
  <td>
    'bla' is not recognized as an internal or external command,operable program or batch file.
  </td>
  
  <td>
    1
  </td>
</tr>

That is it for this post; as you can see there is a way to grab the error message from xp_cmdshell

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum