Using sqlcmd with a batch scheduler like Control-M

A recent project that I worked on required that I run some sql scripts (targeting sql server) in Control-M. The obvious thing to do then is to save the scripts in a directory on the same host as the agent and use sqlcmd to run the script and send the output to a file. Looking at the usage for sqlcmd showed that there is a -o flag to specify the output file. So far, so good. I configured my job and ran it, getting a successful execution which seemed odd as I had deliberately altered the SQL statement so it would fail, to validate that the job would report errors. A look in the sysout displayed no error messages. After a bit of head scratching, I had a look in the output file (configured by the -o flag) and found the error message in there. This is not very useful when you are trying to automate something, so I needed to find another way. There is no option with sqlcmd to send output and errors to different locations, so you need to use DOS to do so. The way to do this is to direct stdout to the output file and stderr to the screen (which in this case is written to the sysout stored by Control-M) The way to do this is to have a command like

sqlcmd -E -S <some server> -d <some database> -i"<some sql file>" -t600 -W -I -b -r0 1>"<some output file>

where the

-i"<some sql file>"

will send the query output to the file specified and all other output ( from the -r0   flags) will go to the screen.

Note also the -b flag - this will stop the script from running when there is an error rather than continuing and will result in sqlcmd ending non 0 - and hence Control-M will know there is a error.