(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: ? .
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 Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.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:
- The first three lines show that the line numbers (on the far left) increment.
- After the "Hello" output the line numbers reset (hence the line numbers are per query batch).
- The first
:on erroris an incomplete command and causes an error. This error neither resets the line number nor exits sqlcmd.
:on error exitis 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.
- The second
:on erroris again incomplete and causes an error. But this time, due to the
:on error exit, sqlcmd displays the error message and immediately exits.
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 Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.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 Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.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.
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:
- we just want to see if including a question mark will produce an error or do something else, and
- 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
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
C:\>"C:\Program Files\Microsoft SQL Server\150\Tools\Binn\OSQL.EXE" ^ More? -E -Q "SELECT 3 ?" [ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error
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 '?'.
C:\>"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.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
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
SQLState = S1000, NativeError = 0 : Unable to resolve column level collations
- bcp utility
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
- Query Analyzer
- osql utility
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:
- This behavior is only found in sqlcmd,
- Both Query Analyzer and osql return an ODBC driver error,
- osql is the deprecated predesecor of the sqlcmd utility,
- sqlcmd also uses ODBC to connect to SQL Server, and
- 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
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.”.
1 thought on “SQLCMD: Prevent an Entire Batch From Even Parsing With One Magical Character (Cruel Joke #4)”
[…] Solomon Rutzky stops SQLCMD in its tracks: […]