SQLCMD in batch mode

[nycdotnet]

Hi - I have a question about using the go-sqlcmd in "batch mode". Sorry for the long explanation, but I promise I will get to the point.

We currently use the "normal" (C++?) version of sqlcmd with a docker image based on the mcr.microsoft.com/mssql/server:2017-latest image on x64 machines (via Docker on Windows or Intel Macs). We process a .sql file to build the schema in the container prior to pushing to our private image registry. We get the .sql file by using SqlPackage.exe in a Windows container against our SQL project which is using SSDT/dacpac. The .sql files start like this:

/*
Deployment script for MyDatabaseName

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;

GO
:setvar DatabaseName "MyDatabaseName"
:setvar DefaultFilePrefix "MyDatabaseName"
:setvar DefaultDataPath "/var/opt/mssql/data"
:setvar DefaultLogPath "/var/opt/mssql/data"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END

GO
USE [master];

This all works fine on the x64 docker image for mcr.microsoft.com/mssql/server:2017-latest.

We have started to get more developers with Apple M1 laptops, and so we wanted to allow those developers to run a copy of our SQL Server DB locally. The mssql/server:2017-latest image doesn't support ARM chips, but FROM mcr.microsoft.com/azure-sql-edge:latest does.

In our dockerfile for the ARM chips, we are now downloading the latest go-sqlcmd via code like this and the download works fine:

RUN curl -L  https://github.com/microsoft/go-sqlcmd/releases/download/v0.10.0/sqlcmd-v0.10.0-linux-arm64.tar.bz2 -o sqlcmd.tar.bz2 && \
    tar -xvjf sqlcmd.tar.bz2 && \
    chmod +x ./sqlcmd && \
    cp -f ./sqlcmd /usr/bin/sqlcmd

We have also set a password in SQLCMDPASSWORD as well as SA_PASSWORD.

However, when we attempt to run /usr/bin/sqlcmd -U SA -i ourscriptfile.sql, we get some errors.

Msg 102, Level 15, State 1, Server buildkitsandbox, Line 2
Incorrect syntax near ':'.
Sqlcmd: Error: Syntax error at line 29 near command ':SETVAR'.
'__IsSqlCmdEnabled' scripting variable not defined.
SQLCMD mode must be enabled to successfully execute this script.

I have seen such SQLCMD errors before, and this seems intentional so that the script breaks if you try to run it within SQL Server Management Studio or something (and not in SQLCMD mode). But is it possible that go-sqlcmd doesn't support sqlcmd mode? Am I missing a new switch or something? I can provide additional details if needed. Thank you for your efforts to bring sqlcmd to new platforms.

By the way, if I run the generated code above using the non-go version of sqlcmd, I get this output, which is what was expected:

Changed database context to 'master'.

If I run go sqlcmd in the x64 SQL Server linux container, I get the same behavior where it doesn't seem to understand SQLCMD.

./sqlcmd --version
sqlcmd: v0.10.0

# this is pre-installed in the mcr.microsoft.com/mssql/server:2017-latest image

/opt/mssql-tools/bin/sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 17.8.0001.1 Linux
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

usage: sqlcmd            [-U login id]          [-P password]
 ... etc ...

[nycdotnet]

I have been messing with this more and it seems like go-sqlcmd doesn't like the :on error exit at all (maybe we can achieve this via -b), and it doesn't seem to allow variables to start with _ (such as __IsSqlCmdEnabled in the above example).

[shueybubbles]

Thx for opening an issue! The :on error exit support was added in #192 to show up in our next release As you found out, the format of variable names is currently too restricted.

[nycdotnet]

Thank you @shueybubbles . I am investigating exporting the scripts via mssql-scripter now rather than sqlpackage.exe to see if it's any better, but is it a reasonable expectation that this project will be fully compatible with the sqlpackage.exe export boilerplate eventually?

[shueybubbles]

our goal is to be nearly 100% compatible with the ODBC sqlcmd, only without the ODBC dependency and to run on more platforms.

We are prioritizing the features folks actually use, so having more users like yourself telling us what is missing will help us a lot.

[nycdotnet]

Thanks for the fast PR and new release!!