SQLCMD fails if -c; is specified in parameters on SQL Server 2019
The following sqlcmd fails with no error and no output, just returns silently.
SQLCMD -U abpsystem -d FKTEST1 -w 255 -h-1 -P manager -S CRKRL-KARFROZA1 -Q "ALTER TABLE xyz ADD abcd NUMERIC(1)" -c;
While it works fine without the
SQLCMD -U abpsystem -d FKTEST1 -w 255 -h-1 -P manager -S CRKRL-KARFROZA1 -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
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
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.
The https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15 doc says:
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 https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver15 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 https://www.quora.com/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.
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.