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

1

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

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.

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:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

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

Join 2,182 other followers

Follow me on Twitter

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