SQL Agent CmdExec gotcha

I was writing a new SQL Agent job for somebody else so I thought I should comment it a bit more thoroughly than I usually do. There’s an Operating System (CmdExec) step that calls sqlcmd to run a script file:

sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b

This pattern works fine elsewhere. Here though, I added a comment just to point out something:

rem A comment
sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b

The job ran successfully but it was suspiciously fast. It turns out that’s because it didn’t do anything. Apparently SQL Agent interprets this differently. I know the DOS shell language is a bit arcane sometimes but rem means ‘ignore everything until the next command’. This works in a .bat file. I just tested to make sure I wasn’t going mad.

Time to dig deeper. Books Online (https://msdn.microsoft.com/en-GB/library/ms190264.aspx) says:

This topic describes how to create and define a Microsoft SQL Server Agent job step in SQL Server 2016 that uses an executable program or operating system command

That ‘an‘ is a subtle clue. I had always assumed this worked like a batch file but it seems only the first command gets executed. In my case the first line is commented out, which effectively means ‘do nothing’. I tested with multiple statements and again only the first one executed.

So don’t confuse it with batch files. Put one command per step and – a bit of lateral thinking here – put your comments in the line(s) underneath:

sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b
rem A comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s