SQLCMD: Prevent an Entire Batch From Even Parsing With One Magical Character (Cruel Joke #4)

(last updated: 2021-09-01 @ 14:18 ET / 2021-09-01 @ 18:18 UTC )

In my previous post, SSMS and SQLCMD: Prevent T-SQL Batch From Not Only Executing, but Also From Parsing (Cruel Joke #3), I talked about voiding an entire query batch in SQL Server Management Studio (SSMS), the sqlcmd utility, and Visual Studio. I discovered that (most likely unintentional) behavior by specifically testing for it. I have used batch repetition / looping for many years and wondered if it would allow me to use the non-intuitive value of "0". In those three programs that I just mentioned, I was able to. But again, that was something I went looking for. On the other hand, sometimes we find things by accident.

This is the story of one such time that I stumbled upon something interesting, and all because I made a mistake. I was doing testing for the previous post (mentioned above) and somehow entered an extra character, or forgot the closing quote on a string literal, or something. Either way, I submitted some T-SQL in the sqlcmd utility that should have produced a parsing error. But instead, it cancelled the entire query batch, and without returning any errors.

I tested in all of the same client programs that I tested with for the previous post, but this time it was only the sqlcmd utility that was affected (which certainly limits the potential for mayhem ??).

So, what is this mysterious, magical character of mystery? (hint: I just used it ??) That’s right, it’s just a simple question mark: ? .

Pre-Test

First, let’s see how sqlcmd behaves normally so that the effect of the question mark will be clearer when we do the actual test. For the pre-test and the actual test I will be using the interactive mode of sqlcmd as it will be easier to see what’s going on due to the line numbering.

Note: I’m only specifying the full path to sqlcmd.exe as I have three versions of it and I want to be certain that I’m using the latest version (indicating that the behavior is current). If you have installed SQL Server, or at least the client tools, or ODBC, then most likely you wouldn’t need to specify the full path (I rarely do).

C:>"C:Program FilesMicrosoft SQL ServerClient SDKODBC170ToolsBinnSQLCMD.EXE" -E
1> SET NOCOUNT ON;
2> SELECT 'Hello';
3> GO
-----
Hello
1> SELECT 123;
2> :on error
Sqlcmd: Error: Syntax error at line 6 near command '
'.
2> :on error exit
2> :on error
Sqlcmd: Error: Syntax error at line 9 near command '
'.
C:>

As you can see:

1. The first three lines show that the line numbers (on the far left) increment.
2. After the "Hello" output the line numbers reset (hence the line numbers are per query batch).
3. The first :on error is an incomplete command and causes an error. This error neither resets the line number nor exits sqlcmd.
4. The :on error exit is a sqlcmd command that instructs the program to exit immediately upon any error, whether it’s a SQL Server error or sqlcmd error. Because this is a sqlcmd command, the batch line number again does not increment.
5. The second :on error is again incomplete and causes an error. But this time, due to the :on error exit , sqlcmd displays the error message and immediately exits.

Tests

Now we get to the good stuff. We are using :on error exit to reduce ambiguity and make it as clear as possible that if anything goes wrong, sqlcmd will exit, and conversely, if sqlcmd does not exit, then there was no error of any kind.

C:>"C:Program FilesMicrosoft SQL ServerClient SDKODBC170ToolsBinnSQLCMD.EXE" -E
1> :on error exit
1> SELECT @
2> GO
Msg 137, Level 15, State 2, Server ALBRIGHT, Line 1
Must declare the scalar variable "@".
C:>

As you can see, there was an error and sqlcmd exited (as was requested on the first line).

And now, for the moment you’ve all been waiting for (drum roll, please)…………………………………………..

C:>"C:Program FilesMicrosoft SQL ServerClient SDKODBC170ToolsBinnSQLCMD.EXE" -E
1> :on error exit
1> SELECT @
2> ?
3> GO
1> quit
C:>

Ta da! Submitting the batch separator (i.e. GO ) clearly completes the batch of statements since the batch line number resets to "1" afterwards. But, this time there’s no error, nor even the slightest indication of anything going wrong, at least not from the perspective of the sqlcmd utility.

Others

Given that GO 0 voided the preceding query batch in three programs, we really should test all the same programs that were tested in the previous post to see if this behavior also happens in any of those.

For the other programs I tested with a slightly different query. The previous test query attempted to produce an error in order to show that including the question mark completely voided the query as no error occurred. The new test query, however, does not attempt to produce an error because:

1. we just want to see if including a question mark will produce an error or do something else, and
2. several programs catch the question mark as a parsing error, in which case if there’s a parsing error that preceds it, then the first error will be noted in the error message, possibly hiding whether the question mark was also a parsing error or simply ignored.

The revised test query is:

SELECT 3 ?
GO

Query Analyzer

[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

osql.exe

C:>"C:Program FilesMicrosoft SQL Server150ToolsBinnOSQL.EXE" ^
More? -E -Q "SELECT 3 ?"
[ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error

isql.exe

Msg 170, Level 15, State 1, Server MONET, Line XXXXX
Line 2: Incorrect syntax near '?'.

SQL Server Management Studio (SSMS) / Visual Studio / Visual Studio Code / Azure Data Studio

Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near '?'.

bcp.exe

C:>"C:Program FilesMicrosoft SQL ServerClient SDKODBC170ToolsBinnbcp.exe" ^
More? "SELECT 3 ?" queryout %TEMP%bcp-test.txt -T -n
Starting copy...
SQLState = 37000, NativeError = 102
Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '?'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unable to resolve column level collations
BCP copy out failed

Behavior by Client

I have tested with the following clients:

• SQL Server Management Studio (SSMS): 18.9.2
• Azure Data Studio (ADS): 1.31.1
• sqlcmd utility: 11.0.2100.60 NT x64 and 15.0.4083.2 NT
• osql utility: 14.0 NT and 15.0 NT (came with SQL Server 2017 and 2019, respectively)
• Visual Studio: 2019 (16.10.4)
• Visual Studio Code: 1.58.2
• Older stuff
  • Query Analyzer: 8.00.194 (came with SQL Server 2000)
  • isql utility: 7.00.623 and 8.00.194 (came with SQL Server 7.0 and 2000, respectively)
  • Microsoft SQL Server Management Studio Express: 9.00.4035.00 (came with SQL Server 2005)
  • Visual Studio: 2015

The following list shows which behaviors can be found in each of those clients:

• Batched is skipped (No indication of any error at all)
  • sqlcmd utility
• Error: Incorrect syntax near '?'.
  • Msg 170, Level 15, State 1
    • isql utility
  • Msg 102, Level 15, State 1
    • SQL Server Management Studio (SSMS)
    • Visual Studio
    • Visual Studio Code
    • Azure Data Studio
    • bcp utility
• Error: SQLState = S1000, NativeError = 0 : Unable to resolve column level collations
  • bcp utility
• Error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
  • Query Analyzer
  • osql utility

Conclusion

Placing a question mark — ? — anywhere in a query batch will cancel the entire batch. This is similar to specifying "0" after the batch separator (as shown in the previous post), except the question mark behavior is specific to the sqlcmd utility.

Whereas the most likely unintentional behavior of GO 0 is probably due to failing to enforce a minimum value on user input, I believe the reason for the "?" behavior is a different type of problem. Given that:

1. This behavior is only found in sqlcmd,
2. Both Query Analyzer and osql return an ODBC driver error,
3. osql is the deprecated predesecor of the sqlcmd utility,
4. sqlcmd also uses ODBC to connect to SQL Server, and
5. SSMS and Visual Studio — both of which appear to use the same T-SQL parser as sqlcmd, but connect to SQL Server via ADO.NET / .NET SqlClient — do not exhibit this behavior

I believe this behavior is the result of the code catching, and then simply swallowing (i.e. ignoring), the COUNT field incorrect or syntax error ODBC error that is returned in the osql utility. Hence, this is a more severe bug than GO 0 since this behavior definitely shouldn’t be happening, as opposed to the probably shouldn’t be happening of GO 0.

Now, unlike all of the other bugs that I’ve found in SQL Server, I highly doubt that I’ll be reporting this bug to Microsoft. Nor am I likely to ever report any other bugs that I find (and I have found others that I just haven’t had a chance to document) because Microsoft has made it quite clear that they simply do not value the time and effort that the SQL Server community has put into reporting bugs and feature requests over the past 10 – 20 years. A few weeks ago they unceremoniously took down their site for reporting such things, and while it was a wretched user experience and overall embarrassment for one of the largest software companies on the planet, it was at least a place to document things. Now, all URLs to the old “feedback” site take you to a page that ironically states, “We value your opinion.”.