Sometime back I was trying to automate certain tasks by using SQL Agent. For a change I thought of accomplishing it using native DOS commands instead of my favorite T-SQL commands. During that I stumbled upon an interesting limitation of CmdExec job steps in SQL Server.
I will explain that using an example. Suppose the requirement is to do the following tasks using native DOS commands.
- Create a folder
- Copy all backup files from one folder to the newly created folder
- List all the files in the newly created folder.
I will create a SQL Agent job which has a CmdExec job step which looks like this
Now I execute the job and check if it has done what was expected. Surprise! I see that the folder with the name FOREXPORT has been created on C drive and it does not have the 3 .bak files from C:\BACKUPS in it. Also the job step output file also does not contain any information. But the job completed successfully.
On closely looking at the job step, it had executed only the first line i.e. MD C:\FOREXPORT and ignored all the other commands below it which are separated by a carriage return.
The workaround for this situation is real simple. Instead of the carriage return separate the commands using &&. Now the job step would look like this
Now the job executes successfully and the contents in the FOREXPORT folder would confirm it.
Creating a batch file which includes all these commands and then executing it through a job would have been far more easier. However this approach exposed a tiny little limitation in SQL Agent job step.