I was working on a SQL Agent job recently that required a series of command shell commands. And it would not work. Days I spent fighting it. I eventually went to sqlhelp. And while no one was able to give me an easy answer directly, Randolph West (b/t) pointed out that I was running a batch. Which lead me to a theory, which I then tested, and I’m now going to share with y’all! Yay us!

Command shell steps in SQL Agent jobs only process the first command. (Yea, I know it’s the title of the post so this wasn’t much of a surprise. You’ll live.)

In order to prove this, I created a job with a series of steps, each one a different test. I’m using two very simple commands. whoami that tells you who is running the shell (very useful if you aren’t certain) and dir which I will assume everyone knows.

Run whoami then dir

Code

whoami
dir c:\temp\test.txt

Output

You’ll notice that the only the whoami was run.

Run dir then whoami

Code

dir c:\temp\test.txt
whoami

Output

And again, just the first line. The dir ran, but not the whoami.

Run whoami then an error

Code

whoami
this isn't a good command

Output

Ok, this one was proof that it’s not even interpreting the second line. That particular line is nonsense and should have thrown an error. But no error, just a valid response from whoami.

Run with a comment on the first line

Code

REM dir
whoami

Output

This one was pretty obvious but still a good test. REM is a commented line in a command shell. You’ll notice that this time absolutely nothing happened. The REM was run but doesn’t do anything so absolutely no result.

Summary

End result, if you want to run more than one command shell statement you either have to use more than one job step or create a .bat (batch) or .cmd (command) file and run it in the job step. And an additional point, if you want to comment your code, do it after the code, not before.