Open highlyunavailable opened 4 years ago
Hi @highlyunavailable, sorry about this. Can you please provide the following:
mssql-cli --version
)pip
was used to install mssql-cli* The script used to repro the error
Any script with invalid syntax works. An example is:
this_is_invalid;
select @@version;
Save to invalid_example.sql.
Run with:
mssql-cli --less-chatty -i invalid_example.sql
I'm providing all server information with environment variables, so add that as appropriate.
Example powershell script to repro:
$env:MSSQL_CLI_SERVER = "yourserver"
$env:MSSQL_CLI_DATABASE = "yourdb"
$env:MSSQL_CLI_USER = "youruser"
$env:MSSQL_CLI_PASSWORD = 'yourpassword'
mssql-cli --less-chatty -i invalid_example.sql
$LASTEXITCODE
This also repros in interactive mode:
my_db> this_is_invalid;
...................... select @@version;
Time: 1.072s (a second)
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'this_is_invalid'.
+--------------------+
| (No column name) |
|--------------------|
| Microsoft SQL Azure (RTM) - 12.0.2000.8
Apr 9 2020 16:39:55
Copyright (C) 2019 Microsoft Corporation
|
+--------------------+
(1 row affected)
my_db>
My config file has:
# Error handling
# When one of multiple SQL statements causes an error, choose to either
# continue executing the remaining statements, or stopping
# Possible values "STOP" or "RESUME"
on_error = STOP
The actual (names obfuscated) command I was running was similar to:
IF (DATABASE_PRINCIPAL_ID('foo') IS NULL)
BEGIN
CREATE ROLE [foo]
AUTHORIZATION [db_owner];
END;
IF (SCHEMA_ID('bar') IS NULL)
BEGIN
EXEC ('CREATE SCHEMA [bar] AUTHORIZATION [dbo];')
END;
GRANT SELECT
ON SCHEMA::[bar] TO [foo];
The actual syntax errors are that there is a semicolon after AUTHORIZATION [db_owner]
and after the END
statements and there apparently shouldn't be.
* Your version of mssql-cli (you can get this by calling `mssql-cli --version`)
Version: 0.18.2
* Your OS
Repros on Windows 10 and Ubuntu 18.04
* Your version of Python, if `pip` was used to install mssql-cli
Python 3.8.2
Here is the fix I added to resolve that:
In 'mssql_cli.py' file, go to line 565 (the if is_error
block), replace the continue
instruction with:
raise Exception(status)
Simple.
Since I'm mostly using mssql-cli in a non-interactive mode, usually embedded in other scripts, this fix suits my needs perfectly, and I don't mind failing after the first error is encountered, as long as the exit-code perfectly reflects the situation.
If I run a file with syntax errors using non-interactive mode, mssql-cli doesn't stop execution after the first error nor does it return a non-zero exit code:
Output looks like: