Command shell steps in SQL Agent jobs only process a single command.


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


dir c:\temp\test.txt


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

Run dir then whoami


dir c:\temp\test.txt


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

Run whoami then an error


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


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.

One thought on “Command shell steps in SQL Agent jobs only process a single command.

  1. […] Kenneth Fisher makes an important discovery about SQL Agent jobs running in command shell mode: […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: