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 "\\127.0.0.1\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 "\\127.0.0.1\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 "\\127.0.0.1\joblogs\somescript.sql" -b rem A comment
David Wimbush 