Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Robocopy within SQL Agent job - headache with error codes! Expand / Collapse
Author
Message
Posted Tuesday, September 21, 2010 3:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:36 AM
Points: 35, Visits: 178
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
Post #990085
Posted Tuesday, September 21, 2010 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 9:30 AM
Points: 2, Visits: 3
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.
Post #990718
Posted Thursday, September 23, 2010 4:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:36 AM
Points: 35, Visits: 178
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
Post #991872
Posted Thursday, January 19, 2012 12:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 26, 2014 1:03 PM
Points: 140, Visits: 389
Thanks for posting this. It's exactly what I needed today.
Post #1239000
Posted Tuesday, November 19, 2013 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:46 AM
Points: 70, Visits: 330
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?
Post #1515537
Posted Wednesday, November 20, 2013 12:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612
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



Post #1516172
Posted Friday, September 26, 2014 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 12:18 PM
Points: 4, Visits: 29
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....
Post #1620305
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse