Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
doodlingdba
doodlingdba
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 213
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
kn0w
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
doodlingdba
doodlingdba
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 213
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
Ken Davis
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 491
Thanks for posting this. It's exactly what I needed today.
wak_no1
wak_no1
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 353
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
RML51
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 1612
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
robert.amborski
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 47
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....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search