Search code examples
sql-serverbatch-filesqlcmd

Sqlcmd: The -h and the -y 0 options are mutually exclusive?

We have an old batch-file (.bat-file) that includes the following sqlcmd code:

sqlcmd -S SERVER_NAME -d DB_NAME -I -Q "SELECT COUNT(*) FROM TableName WHERE ColumnName = 'SpecificValue'" -h-1 -k2 -y0

This code has been running on a Windows Server 2012 for years without any problems. Now we are migrating to a new server with Windows Server 2016 and the above sqlcmd-code no longer works and returns the message: 

"Sqlcmd: The -h and the -y 0 options are mutually exclusive."

A couple of questions:

  • Why does this scenario occur on the Windows Server 2016 server and not on the Windows Server 2012 server? I’m not able to find any documenation or information that the -h and the –y0 options are mutually exclusive?
  • If the -h and the -y 0 options now are mutually exclusive, does this mean that–y0 officially will suppress a header the same way that –h does? I.e. can we remove the ”-h-1” part of the code and expect the same results with only ”-y0”?

SQL Server versions the different servers:

  • SQL Server Version on the Windows Server 2012: 11.0.7462.6

  • SQL Server Version on the Windows Server 2016: 13.0.52.16.0

Solution

  • As I mentioned in my comment, the easiest way to find out if only using one of the switches gives the same output is to actually test. I tested this both on Windows (Version 14.0.3026.27 NT) and Ubuntu (Version 17.2.0000.1 Linux) for completion and got the following results:

    Windows:

    PS Z:\> sqlcmd -S "srvsql2012dev\Sandbox" -E -Q "SELECT 1 AS One;" -h-1 -y0
Sqlcmd: The -h and the -y 0 options are mutually exclusive.
PS Z:\> sqlcmd -S "srvsql2012dev\Sandbox" -E -Q "SELECT 1 AS One;" -h-1
          1

(1 rows affected)
PS Z:\> sqlcmd -S "srvsql2012dev\Sandbox" -E -Q "SELECT 1 AS One;" -y0
1

(1 rows affected)

    Ubuntu:

    larnu@qebui:~$ sqlcmd -S "localhost" -U larnu -Q "SELECT 1 AS One;" -h-1 -y0
Sqlcmd: The -h and the -y 0 options are mutually exclusive.
larnu@qebui:~$ sqlcmd -S "localhost" -U larnu -Q "SELECT 1 AS One;" -h-1
Password:
          1

(1 rows affected)
larnu@qebui:~$ sqlcmd -S "localhost" -U larnu -Q "SELECT 1 AS One;" -y0
Password:
1

(1 rows affected)

    Note that there is a slight difference in the indentation. I don't know what your output looked like before, but i suspect one of these replicates the output you had before.