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

sql 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
  


<div class="tables">
  <table cellpadding="1" cellspacing="1" border="1">
    <tr>
      <th>
        ErrorMessage
      </th>
      
      <th>
        ReturnCode
      </th>
    </tr>
    
    <tr>
      <td>
        The system cannot find the path specified.
      </td>
      
      <td>
        1
      </td>
    </tr>
  </table>
</div>

Change 

sql
SELECT @cmdline = 'dir z:'

to

sql SELECT @cmdline = ‘bla bla z:’ ```

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

ErrorMessage ReturnCode
'bla' is not recognized as an internal or external command,operable program or batch file. 1

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