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

  • doodlingdba

    Hall of Fame

    Points: 3120

    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

  • kn0w

    SSC Enthusiast

    Points: 108

    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: 3120

    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

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

  • wak_no1

    SSCrazy

    Points: 2400

    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

    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

    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....

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

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