Monthly Archives: October 2015

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 "\\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
Advertisements

SQL download file names

When you go to download SQL Server cumulative updates the options can be pretty baffling. Which file(s) do you need? Here’s some help.

For example, if you go to SQL Server 2014 CU1 you are offered these files:

  • Update_SQL2014_RTM_CU1_2931693_12_0_2342_x64
  • 2014_RTM_AOrcleDes_CU1_2931693_12_0_2342_x64
  • 2014_RTM_AOrcleSer_CU1_2931693_12_0_2342_x64

There’s no explanation on the page but presumably it’s explained somewhere. It is, but it’s not easy to find. I asked on the MSDN forum and was pointed to this page which explains it quite clearly.