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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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
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

ErrorMessage ReturnCode
The system cannot find the path specified. 1

Change

T-SQL
1
SELECT @cmdline = 'dir z:'
SELECT @cmdline = 'dir z:'

to

T-SQL
1
SELECT @cmdline = 'bla bla z:'
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