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
|The system cannot find the path specified.||1|
SELECT @cmdline = 'dir z:'
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