SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...