Using Robocopy within SQL Agent job - headache with error codes!

Question

Using Robocopy within SQL Agent job - headache with error codes!

doodlingdba

Hall of Fame

Points: 3164
More actions
September 21, 2010 at 3:47 am

#227033

Hi all
Some of you will know the headaches of using Robocopy within a SQL Agent job via a windows batch file. I am setting this up for the first time and my problem is getting the SQL job to fail when there is a genuine failure, and pass when there is a genuine pass. At the moment it seems to be all or nothing!
All my robocopy job does is copy a file from source to destination. Based on some other forum posts i read, and the robocopy documentation, this is what i've come up with:
call c:\robocopy\robocopy c:\SourceTest\ \\QHSW20820\DestTest\ /NP /MAXAGE:1 /R:1
IF ERRORLEVEL 16 GOTO Label16
IF ERRORLEVEL 8 GOTO Label8
IF ERRORLEVEL 4 GOTO Label4
IF ERRORLEVEL 2 GOTO Label2
IF ERRORLEVEL 1 GOTO Label1
IF ERRORLEVEL 0 GOTO Label0
:Label16
SET ERRORLEV=16
ECHO ERRORLEVEL = %ERRORLEV%
EXIT 16
:Label8
SET ERRORLEV=8
ECHO ERRORLEVEL = %ERRORLEV%
EXIT 8
:Label4
SET ERRORLEV=4
ECHO ERRORLEVEL = %ERRORLEV%
GOTO End
:Label2
SET ERRORLEV=2
ECHO ERRORLEVEL = %ERRORLEV%
GOTO End
:Label1
SET ERRORLEV=1
ECHO ERRORLEVEL = %ERRORLEV%
GOTO End
:Label0
SET ERRORLEV=5
ECHO ERRORLEVEL = %ERRORLEV%
GOTO End
:End
Based on the above, I would like the SQL job to fail if either error 16 or 8 are met. For all other error outputs I want the job to succeed. However, if i set the 'Process exit code of a successful command' to 1 in the SQL agent job, the job always fails regardless of the error code returned.
If I set the 'Process exit code of a successful command' to 0, the job always succeeds.
PLEASE HELP!
Thanks
Doodles

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kn0w SSC Enthusiast Points: 108 More actions · Answer 1

In SMSS you can only specify a single exit code, so you could just pick one say 8 then in your batch file test for %errorlevel% and if it's an 8 or 16 just "Exit 8". Then the SQLAgent will fail the job on either 8 or 16. You would have to have a separate log to place the real error if you need the detail.

doodlingdba Hall of Fame Points: 3164 More actions · Answer 2

Thanks for that - it kind of put me on the right track - i think. Although this is what my final solution looks like:

call c:\robocopy\robocopy c:\Source\ \\SomeShare\Destination\ /NP /MAXAGE:1 /R:1 /PURGE

IF ERRORLEVEL 16 GOTO LabelErr

IF ERRORLEVEL 8 GOTO LabelErr

IF ERRORLEVEL 0 GOTO Label0

:LabelErr

SET ERRORLEV=1

ECHO ERRORLEVEL = %ERRORLEV%

EXIT 1

:Label0

SET ERRORLEV=0

ECHO %ERRORLEV%

I then leave the agent job with Process Exit Code of Successful Command as 0. This seems to work OK. Any serious faults such as permission issues cause the SQL agent job to fail (I write out to a log for specific error details) and if the job copies a new file or does nothing if the dest file is as recent as the source, the job reports as successful.

If any one sees any flaws with this please let me know - for the moment it seems to work how i need it to.

Thanks

Doodles

Ken Davis SSCarpal Tunnel Points: 4329 More actions · Answer 3

Thanks for posting this. It's exactly what I needed today.

wak_no1 SSCrazy Points: 2400 More actions · Answer 4

I'm having the exact same issue!

My problem is that I've got round the exit code issue by this:

ROBOCOPY D:\filetocopy E:\filetocopyto ^& IF %ERRORLEVEL% LEQ 1 exit 0 /MAXAGE:1

The problem which I'm having now is that the '/MAXAGE:1' part of the syntax is being ignored when ran, this is important part of the syntax for me but I also need Robocopy to exit with a code 0 when successful! What am I missing?

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 5

I put the following into a bat file and then execute that from my SQL Server Agent job...

rem RoboCopyErrors.bat

robocopy %*

rem suppress successful robocopy exit statuses, only report genuine errors (bitmask 16 and 8 settings)

set/A errlev="%ERRORLEVEL% & 24"

rem exit batch file with errorlevel so SQL job can succeed or fail appropriately

exit/B %errlev%

I found this here: http://weblogs.sqlteam.com/robv/archive/2010/02/17/61106.aspx

robert.amborski Valued Member Points: 58 More actions · Answer 6

I don't think this works - bat file seems to always return code 0. Just try to remove everything (or REM) in the file but the actual robocopy command....