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
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:'
sql SELECT @cmdline = ‘bla bla z:’ ```
Run the code again and now you should see the following output.
|'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