April 23, 2018 by Kenneth Fisher
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
whoami dir c:\temp\test.txt
You’ll notice that the only the whoami was run.
Run dir then whoami
dir c:\temp\test.txt whoami
And again, just the first line. The dir ran, but not the whoami.
Run whoami then an error
whoami this isn't a good command
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
REM dir whoami
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.
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.