question

stoshw17 avatar image
stoshw17 asked

sql agent job/xp_cmdshell batch file: How to capture output and/or error?

I am using a sql agent job that uses xp_cmdshell to execute a batch file. The batch file runs several sql scripts using sqlcmd. It appears to be working. However, I am unable to capture any error. If I force one of the sql statements to fail, the sql job still finishes successfully. It doesn't appear to see the error the batch file would typically show had I executed it via a command prompt. Is there a way to capture the dos and sql print statements that you would see if you were to run it via a command prompt? And to capture an error? My T-sql looks like this: SELECT @cmd= ProcessPath+ProcessFilename FROM AgentJobProcessPath EXECUTE master..xp_cmdshell @cmd I have tried several other things to get the output from the batch file execution to display in the job history or elsewhere. Below are the things I've already tried. I have tried: CREATE TABLE #output (OutputField nvarchar(4000)) Insert into #output (OutputField) EXECUTE master..xp_cmdshell @cmd SELECT OutputField FROM #Output WHERE OutputField is not null When I try this, and view my job history, there is still no output; also tried: DECLARE @result int; EXEC @result = xp_cmdshell @cmd; IF (@result = 0) PRINT 'Success' ELSE PRINT 'Failure'; In this, @result is ALWAYS 1 even when the sql does NOT produce an error. also tried: SELECT @cmd = @PathFile + ' > \\networkpath\folder\ output.txt' This created the output.txt file, however, it was always empty; also tried: In the SQL Agent job step itself, I went to the advanced tab, and checked the 'Include step output in history'. But there is still no output from the batch file there to view. When I viewed the job history for the step, it was still mostly empty, no output from the batch file. Thank you in advance for any assistance, it is much appreciated!! Stacy
sql agentxp_cmdshell
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.