Can I get an error return by xp_cmdshell?

[LenaK]

I have problem executing xp_cmdshell (I think). In stored procedure I need to create files using bcp. Process should work as following: text file is created in temp folder using bcp, then file is moved from temp to permanent location where it's picked up by another application.
This is supposed to be automated, continuously running process. But once in a while files do not moved (bcp created files successfully in temp folder, but then error accrue and procedure terminated). As a result I have files piled up in temp folder. I tried to trace this problem, but all I could see at the time of error is 'Severity 25' and no error description.
The weird thing about this problem is: it doesn't happened all the time, just once in a while.
This is code I'm using:
-------------------------------------------
--creates file in 'temp' folder
set @strCmd='bcp "select Line from BDName.dbo.tempToFile" queryout '+ @PathName+ 'Temp\' + @FileName + ' -c -a 30000 -S SERVERNAME '
EXEC master..xp_cmdshell @strcmd

--moves file from temp folder to 'PathName' folder
set @strCmd='move '+@PathName+'Temp\' + @FileName + ' ' + @PathName + ' '
EXEC master..xp_cmdshell @strcmd
--------------------------------------------
Could anybody please help me to resolve this problem - I'm new at this and I don't even know how to find out what the error/cause of the problem is.
Thank you very much,
Lena

 

[JamesLean]

You can check the return code of the xp_cmdshell SP and take action if it errors. Any return other than 0 indicates an error:

DECLARE @ret int

EXEC @ret = master..xp_cmdshell @strcmd

IF @ret <> 0
BEGIN
  --do something here, send warning email etc..
END

--James

 

[LenaK]

Thank you James!
I already created process which notifies me if there is a problem. But what I would like to achieve is to find out what causing this problem to happened, like actual error message. I changed my code (see bellow) to insert output from xp_cmdshell to log table. I don't know if it's going to capture errors or session will be terminated before going into these steps (Problem didn't happend since I change code). Any other suggestions ? - Thank you.
--------------------------------------------------
set @strCmd='move '+@ToIRMS_Path+'Temp\' + @FileName + ' ' + @ToIRMS_Path + ' '

CREATE TABLE #cmdout (ProblemDescr nvarchar(255))
INSERT INTO #cmdout (ProblemDescr)
EXEC @result= master..xp_cmdshell @strcmd

IF (@result =1 )
begin
set @problemNo=1+(select max(problemNo) from IRMSInterface_ErrorTrace)
insert into IRMSInterface_ErrorTrace (ProblemNo,ProblemDescr,ProblemDate, DirName) SELECT @problemNo,ProblemDescr,getdate(), @strcmd FROM #cmdout
end
DROP TABLE #cmdout
--------------------------------------------------------