SQLCMD fails if -c; is specified in parameters on SQL Server 2019

Question

The following sqlcmd fails with no error and no output, just returns silently.

SQLCMD -U dbuser -d dbname -w 255 -h-1 -P mypassword -S dbserver -Q "ALTER TABLE xyz ADD abcd NUMERIC(1)" -c;

While it works fine without the -c; argument

SQLCMD -U dbuser -d dbname -w 255 -h-1 -P mypassword -S dbserver -Q "ALTER TABLE xyz ADD abcd NUMERIC(1)"

This parameter has been there since before 2005, with OSQL, then recently (less than a year ago) we replaced OSQL with SQLCMD.

And now we have discovered that one of our install scripts is not executing and it has come down to this -c; argument.

Also, I am testing on SQL Server 2019. I asked someone to execute the same scripts on a SQL Server 2008 and there the SQLCMD is working with the -c; parameter.

I have even tried specifying : and a few other characters instead of ; to no avail.

How should this parameter be used? And if it is not recommended or not supported to use it any more, then please point to any documentation/bug report/release notes.

• Ronaldo: Could you please check your SQLCMD version? Also, could you try adding a space between the -c and the character (other than the semicolon)?
• Ozair Kafray: @Ronaldo - My sqlcmd version is 15. 0.2000.5 NT. I had already tried ^, :, _ as terminators with and without space after -c. This parameter doesn't make sense to me anymore.

Answer 1

The sqlcmd Utility doc says:

-c batch_terminator
Specifies the batch terminator. By default, commands are terminated and sent to SQL Server by typing the word "GO" on a line by itself. When you reset the batch terminator, do not use Transact-SQL reserved keywords or characters that have special meaning to the operating system, even if they are preceded by a backslash. [emphasis mine]

And if you see the Transact-SQL Syntax Conventions (Transact-SQL) doc, you'll find that:

; Transact-SQL statement terminator. Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version.

That info added to what Stewart Gordon pointed on Why should I use a semicolon in SQL?:

The ANSI/ISO standards for SQL require statements to be separated by semicolons. As such, the majority of SQL implementations will require them. I think Microsoft SQL Server 2012 and earlier is one of the few that deviates from this standard.

From that I'd say the semicolon is now a reserved keyword, although it's not required yet on T-SQL.

---

How to show the error message

Error Reporting Options
-b Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0. If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd does not report errors for severity level 10 (informational messages).

If the sqlcmd script contains an incorrect comment, syntax error, or is missing a scripting variable, ERRORLEVEL returned is 1.

-m error_level
Controls which error messages are sent to stdout. Messages that have a severity level greater than or equal to this level are sent. When this value is set to -1, all messages including informational messages, are sent. Spaces are not allowed between the -m and -1. For example, -m-1 is valid, and -m -1 is not.

This option also sets the sqlcmd scripting variable SQLCMDERRORLEVEL. This variable has a default of 0.

Answer 2

When you look at the help from sqlcmd /?, the -c switch is listed as [-c cmdend], which isn't helpful.

However, if you look at the docs, there is a much more helpful description:

-c batch_terminator - Specifies the batch terminator. By default, commands are terminated and sent to SQL Server by typing the word "GO" on a line by itself. When you reset the batch terminator, do not use Transact-SQL reserved keywords or characters that have special meaning to the operating system, even if they are preceded by a backslash.

By passing -c; you're telling sqlcmd to send everything between ;s to the server to execute it as separate batches. Since ; is the (optional) statement terminator for SQL (and assuming that every statement does use the statement terminator), you're basically telling sqlcmd to send every statement as a separate batch, similar to having a GO after every statement.

Personally, I find code that uses non-standard batch terminators to be confusing. If you need every statement to be sent as a separate batch, insert GOs between them, and do not use the -c switch. That way, it will work with all the defaults in SSMS, sqlcmd, and other client tools.