Catching Error Message from XP_CMDSHELL

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 '\server\directory\filename'. NULL

How do I capture the f开发者_C百科irst 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?

The error comes from the command shell itself, it is not a SQL Server error

one way to grab the error is

declare @cmdline varchar(500),@ReturnCode int
select @cmdline = 'dir f:'

create table #temp (SomeCol varchar(500))


insert #temp
EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

if @ReturnCode <> 0
select * from #temp
where SomeCol is not null

but of course if you do DIR C: the table will be filled with all the files and folders from that command