Michael J. Swart

September 27, 2012

My Short Struggle With SQL Agent

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

You know I’ve always thought of SQL Agent as one of the “Agent Programs” from the movie the Matrix:

So what follows is a mini-lesson about the SQL Agent. I recently got into trouble when I created a SQL Agent Job step. The only thing this step needed to do was to move some text files from one network drive to another.

I just wanted to use the dos command:

move "\\server1\somedir\*.txt" "\\server2\somedir"

But if there were no files copied, the step failed because the return code was 1 instead of 0. It succeeded in copying all the files that were there (all zero of them), but apparently that’s unacceptable.

I then tried using robocopy. Robocopy is the “robust copy” command for Windows. It’s got all the bells and whistles… so I tried:

robocopy "\\server1\somedir\" "\\server2\somedir\" *.txt /mov

but the return code for robocopy is very complicated. Even more so than regular move. I’m not the first to deal with this headache… Check out this topic on SQL Server Central’s forum. The solution there uses a batch script to accomplish the task. If it’s successful, it returns a consistent error code. But doesn’t it seem like there should be a better way?

Only One Successful Return Code For Cmd Steps

Yep, the problem stems from the fact that only one return code is allowed for SQL Agent Job steps that are Operating System commands. That’s shown on this screen:

There can be only one ... error code.

Powershell Beats ’em All

Long story short… I went with a powershell script. It was the simplest:

move-item "\\server1\somedir\*.txt" "\\server2\somedir"

And I don’t worry about return codes here. The powershell script succeeds or it doesn’t. Times when the script fails include when the move would overwrite an existing file or when permissions prevent the file move and that’s what I wanted.

In powershell, simple things are simple. Hard things are possible.


  1. OH MAN!
    I forgot to colour his eyebrows… Well just imagine they’re brown.

    Comment by Michael J. Swart — September 28, 2012 @ 9:33 am

  2. Haha loving the matrix code behind Agent Smith, very appropriate use of SQL’s Special Data functions 😉

    Comment by Links — October 1, 2012 @ 9:51 am

  3. Thanks Links.

    That query in the background is actually from this post More Images From the Spatial Results Tab. I thought it was appropriate since it’s actually code for a red-head. 😛

    Comment by Michael J. Swart — October 1, 2012 @ 10:21 am

  4. […] My Short Struggle With SQL Agent – A swift look at a particularly frustrating development constraint imposed by Agent Smith the SQL Server Agent, it’s Michael J. Swart (Blog|Twitter). […]

    Pingback by Something for the Weekend - SQL Server Links 05/10/12 — October 5, 2012 @ 4:59 am

  5. […] Shamelessly borrowed from the Database Whisperer […]

    Pingback by SQL Server for the IT Helpdesk – nate_the_dba — October 13, 2016 @ 4:25 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress