Catching Error Message from XP_CMDSHELL

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

Answer 1

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