Michael J. Swart

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:

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.