microsoft / go-sqlcmd

The new sqlcmd, CLI for SQL Server and Azure SQL (winget install sqlcmd / sqlcmd create mssql / sqlcmd open ads)

SQLCMD CLI

This repo contains the sqlcmd command line tool and Go packages for working with Microsoft SQL Server, Azure SQL Database, and Azure Synapse.

Learn more about how sqlcmd is used from a articles/posts written by the community: Community Buzz.

Installation

sqlcmd is available in package managers for all major platforms.

Windows

sqlcmd is available via Winget, Choco and as a downloadable .msi or .zip from the releases page. The .msi installer is signed with a Microsoft Authenticode certificate.

WinGet

Install:	Upgrade:
winget install sqlcmd	winget upgrade sqlcmd

Choco

Install:	Upgrade:
choco install sqlcmd	choco upgrade sqlcmd

macOS

sqlcmd is available via Homebrew, and as a downloadable .tar from the releases page.

Homebrew

Install:	Upgrade:
brew install sqlcmd	brew upgrade sqlcmd

Apple Silicon Macs (M1/M2)

Macs running Apple Silicon require Docker Desktop to use Rosetta for x86/amd64 emulation. Follow these steps before creating a SQL Server instance:

• Open Docker Desktop.
• Go to the settings/preferences menu.
• Find the “Features in development” section.
• Enable the "Use Rosetta for x86/amd64 emulation on Apple Silicon" checkbox.

Linux

sqlcmd is available via Linuxbrew, and as a downloadable .rpm/.deb and .tar from the releases page.

On Linux, sqlcmd is also available through apt-get, yum and zypper package managers. Instructions can be found here.

Linuxbrew

The Homebrew package manager may be used on Linux and Windows Subsystem for Linux (WSL) 2. Homebrew was formerly referred to as Linuxbrew when running on Linux or WSL.

Install:	Upgrade:
brew install sqlcmd	brew upgrade sqlcmd

Use sqlcmd to create local SQL Server and Azure SQL Edge instances

Use sqlcmd to create SQL Server and Azure SQL Edge instances using a local container runtime (e.g. Docker or Podman)

Create SQL Server instance using local container runtime and connect using Azure Data Studio

To create a local SQL Server instance with the AdventureWorksLT database restored, query it, and connect to it using Azure Data Studio, run:

sqlcmd create mssql --accept-eula --using https://aka.ms/AdventureWorksLT.bak
sqlcmd query "SELECT DB_NAME()"
sqlcmd open ads

Use sqlcmd --help to view all the available sub-commands. Use sqlcmd -? to view the original ODBC sqlcmd flags.

The ~/.sqlcmd/sqlconfig file

Each time sqlcmd create completes, a new context is created (e.g. mssql, mssql2, mssql3 etc.). A context contains the endpoint and user configuration detail. To switch between contexts, run sqlcmd config use <context-name>, to view name of the current context, run sqlcmd config current-context, to list all contexts, run sqlcmd config get-contexts.

To view connection strings (ODBC/ADO.NET/JDBC etc.) for the current context and user & endpoint details for all contexts held in the ~/.sqlcmd/sqlconfig file:

sqlcmd config connection-strings
sqlcmd config view

Versions

To see all version tags to choose from (2017, 2019, 2022 etc.), and install a specific version, run:

SET SQLCMD_ACCEPT_EULA=YES

sqlcmd create mssql get-tags
sqlcmd create mssql --tag 2019-latest

To stop, start and delete contexts, run the following commands:

sqlcmd stop
sqlcmd start
sqlcmd delete

Backwards compatibility with ODBC sqlcmd

To connect to the current context, and use the original ODBC sqlcmd flags (e.g. -q, -Q, -i, -o etc.), which can be listed with sqlcmd -?, run:

sqlcmd -q "SELECT @@version"
sqlcmd

If no current context exists, sqlcmd (with no connection parameters) reverts to the original ODBC sqlcmd behavior of creating an interactive session to the default local instance on port 1433 using trusted authentication, otherwise it will create an interactive session to the current context.

Sqlcmd

The sqlcmd project aims to be a complete port of the original ODBC sqlcmd to the Go language, utilizing the go-mssqldb driver. For full documentation of the tool and installation instructions, see go-sqlcmd-utility.

Changes in behavior from the ODBC based sqlcmd

• / is not accepted as a flag specifier, only -
• There are new posix-style versions of each flag, such as --input-file for -i. sqlcmd -? will print those parameter names. Those new names do not preserve backward compatibility with ODBC sqlcmd. For example, to specify multiple input file names using --input-file, the file names must be comma-delimited, not space-delimited.

The following switches have different behavior in this version of sqlcmd compared to the original ODBC based sqlcmd.

• -R switch is ignored. The go runtime does not provide access to user locale information, and it's not readily available through syscall on all supported platforms.
• -I switch is ignored; quoted identifiers are always set on. To disable quoted identifier behavior, add SET QUOTED IDENTIFIER OFF in your scripts.
• -N now takes an optional string value that can be one of s[trict],t[rue],m[andatory], yes,1, o[ptional],no, 0, f[alse], or disable to specify the encryption choice.
  • If -N is passed but no value is provided, true is used.
  • If -N and -C are not provided, sqlcmd will negotiate authentication with the server without validating the server certificate.
  • If -N is provided but -C is not, sqlcmd will require validation of the server certificate. Note that a false value for encryption could still lead to encryption of the login packet.
  • -C has no effect when strict value is specified for -N.
  • If both -N and -C are provided, sqlcmd will use their values for encryption negotiation.
  • More information about client/server encryption negotiation can be found at https://docs.microsoft.com/openspecs/windows_protocols/ms-tds/60f56408-0188-4cd5-8b90-25c6f2423868
• -u The generated Unicode output file will have the UTF16 Little-Endian Byte-order mark (BOM) written to it.
• Some behaviors that were kept to maintain compatibility with OSQL may be changed, such as alignment of column headers for some data types.
• All commands must fit on one line, even EXIT. Interactive mode will not check for open parentheses or quotes for commands and prompt for successive lines. The ODBC sqlcmd allows the query run by EXIT(query) to span multiple lines.
• -i doesn't handle a comma , in a file name correctly unless the file name argument is triple quoted. For example: sqlcmd -i """select,100.sql""" will try to open a file named sql,100.sql while sqlcmd -i "select,100.sql" will try to open two files select and 100.sql
• If using a single -i flag to pass multiple file names, there must be a space after the -i. Example: -i file1.sql file2.sql
• -M switch is ignored. Sqlcmd always enables multi-subnet failover.

Switches not available in the new sqlcmd (go-sqlcmd) yet

There are a few switches yet to be implemented in the new sqlcmd (go-sqlcmd) compared to the original ODBC based sqlcmd, discussion #293 lists these switches. Please provide feedback in the discussion on which switches are most important to you to have implemented next in the new sqlcmd.

Miscellaneous enhancements

• Console output coloring (see below)
• :Connect now has an optional -G parameter to select one of the authentication methods for Azure SQL Database - SqlAuthentication, ActiveDirectoryDefault, ActiveDirectoryIntegrated, ActiveDirectoryServicePrincipal, ActiveDirectoryManagedIdentity, ActiveDirectoryPassword. If -G is not provided, either Integrated security or SQL Authentication will be used, dependent on the presence of a -U username parameter.
• The new --driver-logging-level command line parameter allows you to see traces from the go-mssqldb client driver. Use 64 to see all traces.
• Sqlcmd can now print results using a vertical format. Use the new -F vertical command line option to set it. It's also controlled by the SQLCMDFORMAT scripting variable.

1> select session_id, client_interface_name, program_name from sys.dm_exec_sessions where session_id=@@spid
2> go
session_id            58
client_interface_name go-mssqldb
program_name          sqlcmd

• sqlcmd supports shared memory and named pipe transport. Use the appropriate protocol prefix on the server name to force a protocol:
  • lpc for shared memory, only for a localhost. sqlcmd -S lpc:.
  • np for named pipes. Or use the UNC named pipe path as the server name: sqlcmd -S \\myserver\pipe\sql\query
  • tcp for tcp sqlcmd -S tcp:myserver,1234 If no protocol is specified, sqlcmd will attempt to dial in this order: lpc->np->tcp. If dialing a remote host, lpc will be skipped.

1> select net_transport from sys.dm_exec_connections where session_id=@@spid
2> go
net_transport Named pipe

Azure Active Directory Authentication

sqlcmd supports a broader range of AAD authentication models (over the original ODBC based sqlcmd), based on the azidentity package. The implementation relies on an AAD Connector in the driver.

Command line

To use AAD auth, you can use one of two command line switches:

-G is (mostly) compatible with its usage in the prior version of sqlcmd. If a username and password are provided, it will authenticate using AAD Password authentication. If a username is provided it will use AAD Interactive authentication which may display a web browser. If no username or password is provided, it will use a DefaultAzureCredential which attempts to authenticate through a variety of mechanisms.

--authentication-method= can be used to specify one of the following authentication types.

ActiveDirectoryDefault

• For an overview of the types of authentication this mode will use, see (https://github.com/Azure/azure-sdk-for-go/tree/main/sdk/azidentity#defaultazurecredential).

• Choose this method if your database automation scripts are intended to run in both local development environments and in a production deployment in Azure. You'll be able to use a client secret or an Azure CLI login on your development environment and a managed identity or client secret on your production deployment without changing the script.

• Setting environment variables AZURE_TENANT_ID, and AZURE_CLIENT_ID are necessary for DefaultAzureCredential to begin checking the environment configuration and look for one of the following additional environment variables in order to authenticate:

  • Setting environment variable AZURE_CLIENT_SECRET configures the DefaultAzureCredential to choose ClientSecretCredential.
  • Setting environment variable AZURE_CLIENT_CERTIFICATE_PATH configures the DefaultAzureCredential to choose ClientCertificateCredential if AZURE_CLIENT_SECRET is not set.
  • Setting environment variable AZURE_USERNAME configures the DefaultAzureCredential to choose UsernamePasswordCredential if AZURE_CLIENT_SECRET and AZURE_CLIENT_CERTIFICATE_PATH are not set.

ActiveDirectoryIntegrated

This method is currently not implemented and will fall back to ActiveDirectoryDefault.

ActiveDirectoryPassword

This method will authenticate using a username and password. It will not work if MFA is required. You provide the username and password using the usual command line switches or SQLCMD environment variables. Set AZURE_TENANT_ID environment variable to the tenant id of the server if not using the default tenant of the user.

ActiveDirectoryInteractive

This method will launch a web browser to authenticate the user.

ActiveDirectoryManagedIdentity

Use this method when running sqlcmd on an Azure VM that has either a system-assigned or user-assigned managed identity. If using a user-assigned managed identity, set the username to the ID of the managed identity. If using a system-assigned identity, leave username empty.

ActiveDirectoryServicePrincipal

This method authenticates the provided username as a service principal id and the password as the client secret for the service principal. Provide a username in the form <service principal id>@<tenant id>. Set SQLCMDPASSWORD variable to the client secret. If using a certificate instead of a client secret, set AZURE_CLIENT_CERTIFICATE_PATH environment variable to the path of the certificate file.

Environment variables for AAD auth

Some settings for AAD auth do not have command line inputs, and some environment variables are consumed directly by the azidentity package used by sqlcmd. These environment variables can be set to configure some aspects of AAD auth and to bypass default behaviors. In addition to the variables listed above, the following are sqlcmd-specific and apply to multiple methods.

SQLCMDCLIENTID - set this to the identifier of an application registered in your AAD which is authorized to authenticate to Azure SQL Database. Applies to ActiveDirectoryInteractive and ActiveDirectoryPassword methods.

Console colors

Sqlcmd now supports syntax coloring the output of :list and the results of TSQL queries when output to the terminal. To enable coloring use the SQLCMDCOLORSCHEME variable, which can be set as an environment variable or by using :setvar. The valid values are the names of styles supported by the chroma styles project.

To see a list of available styles along with colored syntax samples, use this command in interactive mode:

:list color

Packages

sqlcmd executable

Build sqlcmd

./build/build.sh

or

.\build\build.cmd

sqlcmd package

pkg/sqlcmd is consumable by other hosts. Go docs for the package are forthcoming. See the test code and main.go for examples of initializing and running sqlcmd.

Building

build/build

Testing

The tests rely on SQLCMD scripting variables to provide the connection string parameters. Set SQLCMDSERVER, SQLCMDDATABASE, SQLCMDUSER, SQLCMDPASSWORD variables appropriately then

go test ./...

If you are developing on Windows, you can use docker or WSL to run the tests on Linux. docker run lets you pass the environment variables. For example, if your code is in i:\git\go-sqlcmd you can run tests in a docker container:

docker run -rm -e SQLCMDSERVER=<yourserver> -e SQLCMDUSER=<youruser> -e SQLCMDPASSWORD=<yourpassword> -v i:\git\go-sqlcmd:/go-sqlcmd -w /go-sqlcmd golang:1.16 go test ./...

Localization

The new sqlcmd (go-sqlcmd) is localized for the following languages: Chinese (Simplified) | Chinese (Traditional) | English (United States) | French | German | Italian | Japanese | Korean | Portuguese (Brazil) | Russian | Spanish

Currently, the user visible strings that also existed in ODBC based sqlcmd are localized in the new sqlcmd, new strings (introduced with the new sqlcmd functionality) will be localized shortly.

To get localized messages from sqlcmd set environment variable SQLCMD_LANG to a language tag as per BCP47 convention. e.g.

\git\go-sqlcmd>set SQLCMD_LANG=de-de
\git\go-sqlcmd>.\sqlcmd.exe -w 4
sqlcmd.exe: error: sqlcmd.exe: '-w 4': Der Wert muss größer als 8 und kleiner als 65536 sein.

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.

go-sqlcmd's Issues

There seems some buffer limitation when use sqlcmd to run query

There seems some kind of buffer limitation when using the sqlcmd tool to run query.

Problem:
When I use sqlcmd to run restore database sql query, it will hang if I keep the STATS=5 keyword in the query (see manual_testing_setup_script.sql below). Also, when use the sqlcmd to run query files with bunch of update query statement, I can only run 4 update statements at a time and it will hang at the fifth update statement.

Reproduce:
Below is the log when try to reproduce the issue. In the Dockerfile, besides copies the sql query file and download the sqlcmd to the container, I will start the server and run a simple restore database query but when it does that, it will hang and stop at 30 percent processed.

For this test, I used Microsoft northwind.bak file.

=> [internal] load .dockerignore                                                                                                                                                         0.0s
 => => transferring context: 2B                                                                                                                                                           0.0s
 => [internal] load metadata for mcr.microsoft.com/azure-sql-edge:latest                                                                                                                  0.0s
 => CACHED [ 1/15] FROM mcr.microsoft.com/azure-sql-edge:latest                                                                                                                           0.0s
 => [internal] load build context                                                                                                                                                         0.0s
 => => transferring context: 758B                                                                                                                                                         0.0s
 => [2/8] COPY --chown=mssql:root manual_testing_setup_script.sql /backup/manual_testing_setup_script.sql                                                                                 0.0s
 => [3/8] COPY --chown=mssql:root northwind.bak /backup/northwind.bak                                                                                                                     0.0s
 => [4/8] COPY --chown=mssql:root restore_db_test.sh /backup/restore_db_test.sh                                                                                                           0.0s
 => [5/8] RUN chmod a+rwx /backup/restore_db_test.sh                                                                                                                                      0.2s
 => [6/8] RUN chmod g+rwx /backup/restore_db_test.sh                                                                                                                                      0.2s
 => [7/8] RUN if [ ! -d /opt/mssql-tools/bin ] ; then         echo '\"/opt/mssql-tools/bin\"" directory not found, download the sqlcmd tool...' >> /tmp/arm64.log         && mkdir -p /o  3.0s
 => [8/8] RUN /backup/restore_db_test.sh                                                                                                                                                276.4s
 => => # 5 percent processed.                                                                                                                                                                 
 => => # 11 percent processed.                                                                                                                                                                
 => => # 15 percent processed.                                                                                                                                                                 
 => => # 20 percent processed.                                                                                                                                                                 
 => => # 26 percent processed.                                                                                                                                                                 
 => => # 30 percent processed.                                                                                                                                                                 

this is my sql query in the manual_testing_setup_script.sql file

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2048;
GO
RECONFIGURE;
GO

RESTORE DATABASE [northwind] 
FROM  
	DISK = N'/backup/northwind.bak' 
WITH  
  FILE = 1,  
  MOVE N'northwind' TO N'/var/opt/mssql/data/northwind.mdf',
  MOVE N'northwind_log' TO N'/var/opt/mssql/data/northwind.LDF',
	NOUNLOAD,
  STATS = 5
GO

If I removed the STATS = 5 in my query, the build will succeed.

Below is the content in my restore_db_test.sh file

export ACCEPT_EULA=Y
export SA_PASSWORD='password1@'
export MSSQL_SA_PASSWORD='password1@'

echo "Starting up sql server..."
/opt/mssql/bin/sqlservr > /backup/startup.log &

echo "  Waiting a few seconds for sql server to start..."
sleep 30

echo "Run sql server setup ....."
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -d master -i /backup/manual_testing_setup_script.sql

The same issue also happened when I try to run multiple DB update statements. I will need to break them down into smaller batches, then it can run successfully without hanging.

Finally, the same issue doesn't happen when I use the Azure Data Studio sqlcmd tool to run the query or to restore the database.

Thank you for your time and help. Please let me know if you need more information.

Original question: #36 (reply in thread)

Add `--version` flag to CLI

Having a --version flag is pretty standard for a CLI as it lets the user know what version of the tool they're using, and hence also helps with bug reports.

> sqlcmd --version
sqlcmd.exe: error: unknown flag --version

Add a vertical layout for sqlcmd results

Add a switch or variable for printing results vertically. Each line would have the column name followed by the value.

sqlcmd input connection string

It isn't always convenient to break connection information into all the command flags - it would streamline these scenarios to be able to pass directly a connection string (ADO .NET format for example) instead.

basic BCP functionality

CSV import export

Consider enabling custom date and time format strings provided by the user

We don't have a way to get the user's locale information, particularly on non-Windows platforms.
As part of the fix for #77 I am going to implement the standard format strings for the TSQL date and time types. It'd be cool if users could provide their own format strings mapped to a given SQL type.

For example, the default format string in go for a DATETIME will be 2006-01-02 15:04:05.000

the time package in Go defines a bunch of constants for common layouts:

const (
	Layout      = "01/02 03:04:05PM '06 -0700" // The reference time, in numerical order.
	ANSIC       = "Mon Jan _2 15:04:05 2006"
	UnixDate    = "Mon Jan _2 15:04:05 MST 2006"
	RubyDate    = "Mon Jan 02 15:04:05 -0700 2006"
	RFC822      = "02 Jan 06 15:04 MST"
	RFC822Z     = "02 Jan 06 15:04 -0700" // RFC822 with numeric zone
	RFC850      = "Monday, 02-Jan-06 15:04:05 MST"
	RFC1123     = "Mon, 02 Jan 2006 15:04:05 MST"
	RFC1123Z    = "Mon, 02 Jan 2006 15:04:05 -0700" // RFC1123 with numeric zone
	RFC3339     = "2006-01-02T15:04:05Z07:00"
	RFC3339Nano = "2006-01-02T15:04:05.999999999Z07:00"
	Kitchen     = "3:04PM"
	// Handy time stamps.
	Stamp      = "Jan _2 15:04:05"
	StampMilli = "Jan _2 15:04:05.000"
	StampMicro = "Jan _2 15:04:05.000000"
	StampNano  = "Jan _2 15:04:05.000000000"
)

We could define either environment variables or create a new config file for sqlcmd where users can set the format string associated with each TSQL date type. If we use environment variables we can incorporate them into the regular set of SQLCMD variables so scripts could assign it using :setvar

sqlcmd prints "rows affected" messages for variable declarations

E:\git\go-sqlcmd\cmd\sqlcmd>.\sqlcmd
1> declare @d1 int =1
2> select @d1 as d
3> go
(1 row affected)
d
-----------
          1

(1 row affected)

r0/r1 (output handling to stderr)

r0 = errors only to stderr
r1 = all messages to stderr

SQLCMD should exit on certain errors

Consider:

RAISERROR('This is the end', 1, 127)

When you run this in the old SQLCMD, the scripts exits, because state is 127. go-sqlcmd does not. (This an old holdover from ISQL, and should still work I think. No, this does not work in SSMS.)

On a similar note:

RAISERROR('Get me out of here!', 20, 1) WITH LOG

This closes the connection to SQL Server, and old SQLCMD exits at this point. go-sqlcmd does not, but nor does it reconnect, so you only get more error messages.

Questions on using ActiveDirectoryManagedIdentity

The documentation states that when ActiveDirectoryManagedIdentity is used, we need to specify the Managed Identity name as the user name in sqlcmd. So I tried:

.\sqlcmd.exe --authentication-method=ActiveDirectoryManagedIdentity -U vm_msi_name -S someserver.database.windows.net

Unfortunately, this errors out with the message The requested identity isn't assigned to this resource

On a hunch, I tried without the -U parameter:

.\sqlcmd.exe --authentication-method=ActiveDirectoryManagedIdentity -S someserver.database.windows.net

... and it worked. So it seems that specifying the managed identity name may not be mandatory. If this is true, can the README / docs be updated?

As a follow up question, if we do need to specify the managed identity name, how can we disambiguate when multiple identities have the same name, but have different client IDs? Can we allow for either the name, or the client ID, being provided to sqlcmd? For example, the Azure Portal Azure Active Directory - All applications blade does allow searching by either name or client ID.

Executing input file that contain strings ending with \

I have a script file that contains strings ending with \. When running sqlcmd -S localhost -U sa -i script.sql, I get the following error:

Incorrect syntax near 'GO'.

These steps should allow to reproduce the issue:

docker run -it -u 0 -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=demo1234!' -p 1433:1433 mcr.microsoft.com/azure-sql-edge /bin/bash
# apt-get update && apt-get install -y curl gnupg2 software-properties-common
# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/prod.list)"
# apt-get update && apt-get install -y sqlcmd
# export SQLCMDPASSWORD=demo1234!
# /opt/mssql/bin/sqlservr &

wait for sql server to start

# sqlcmd -l 1 -S localhost -U sa -Q "SELECT 1"

create script.sql with these contents

-- script.sql


USE [master]
GO
IF DB_ID (N'demo') IS NOT NULL
DROP DATABASE [demo];
GO
CREATE DATABASE [demo]
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled\'))
begin
EXEC [demo].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

# sqlcmd -S localhost -U sa -i script.sql

and you should see the Incorrect syntax near 'GO'. error.

Any thoughts on what I can do to overcome this issue ?

Deploy sqlcmd binary to appropriate package managers

Sqlcmd users should be able to install it on Linux the same way they install SQL Server.
What package managers should we target on Windows/Linux/Mac?
@dzsquared

sqlcmd -? does not work and -h does the wrong thing.

If I run sqlcmd -? with the established SQLCMD I get a list of option. If I run this with go-sqlcmd, I get:

sqlcmd: error: unknown flag -?, did you mean one of "-h", "-c", "-C", "-d", "-E", "-U", "-i", "-o", "-q", "-Q", "-S", "-X", "-G", "-x", "-v", "-a", "-l", "-H", "-K", "-N", "-b", "-V", "-m", "-F", "-r"?

sqlcmd -h or sqlcmd --help gives the desired result. However, -h has an established meaning with SQLCMD to specify how often you want headers printed. I would guess that there quite a few bat files out there with SQLCMD -h-1 in them. These are people who use SQLCMD to export data and the argument -1 suppresses the headers entirely.

--help can still be used for help, but it is not a good idea to use -h.

SQLCMD must stay connected

I started SQLCMD. Then I entered:

1> USE Northwind
2> go
Changed database context to 'Northwind'.
1> SELECT * FROM "Order Details"
2> go
Msg 208, Level 16, State 1, Server SOMMERWALD, Line 1
Invalid object name 'Order Details'.

And I thought "What?".

Profiler revealed what is going on. For every batch you are connecting and disconnecting. So for the SELECT statement I was back in my default database.

That does not fly. SQLCMD is like SSMS. Once, I have connected, I should stay connected. The above is only one example of what goes wrong. I may create a temp table which I want to use in the next batch. This fails. I may start a transaction that I want to run over multiple batches. But that transaction is rolled back with the first batch. (Which means that only the rest of the transaction is performed, which is really bad.)

(To be precise, SSMS has an option that permits you to disconnect on every query. But that is not a default setting.)

Add Windows icon and version resources

Using https://github.com/tc-hib/go-winres

ADO task for go-sqlcmd execution

examples here: https://github.com/microsoft/azure-pipelines-tasks

current sqlcmd task: https://github.com/microsoft/azure-pipelines-tasks/tree/master/Tasks/SqlAzureDacpacDeploymentV1

Scripting variable names should include underscore

Scripting variable names currently allows a-z A-Z 0-9 only. The standard sqlcmd tool is not as strict and .sql-files using underscore in variables fail with syntax error.

The relevant code is in parse.go function readVariableReference.

Take this example (the escaped dollar character is to prevent linux shell from variable substitution):

$ sqlcmd -U SA -v "FOOBAR"="testdb" -Q "create database \$(FOOBAR);"

$ sqlcmd -U SA -v "FOO_BAR"="testdb" -Q "create database \$(FOO_BAR);"
Sqlcmd: Error: Syntax error at line 1.

I haven't found any reference documentation on what characters the standard sqlcmd tool allows other than: https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15#guidelines-for-scripting-variable-names-and-values

It appears to be very lenient stating only these three guidelines:

• Variable names must not contain white space characters or quotation marks.
• Variable names must not have the same form as a variable expression, such as $(var).
• Scripting variables are case-insensitive

If compatibility with standard sqlcmd name rules are problematic at least consider adding underscore as an allowed character since it is frequently used at least when resolving scripting variables from environment variables.

No newline at end of stderr output (sqlcmd -r)

When stderr (-r0 switch) is outputted to console, there is no newline at the end, so when go-sqlcmd exists, the console prompt appears on the same line as the last line of stderr.

-b (terminate batch job if there is an error)

Backslashes in cmdline-option arguments causes option to be ignored

When I try:

sqlcmd -S SERVER\INSTANCE

all that happens is that I get the command-line prompt again. SQLCMD does not start and there is no error message. It does not matter if the server or instance exists, behaviour is the same.

In the same vein, if I do:

sqlcmd -i C:\temp\slask.sql

The result is the same. That is, SQLCMD does not start, and there is no error message. Again, it does not matter if the file exists or not.

I don't know these are two separate defects or tokens of the same issue, but submit it as a single one.

Define a settings file for sqlcmd app

Trying to manage every possible setting through command line switches and environment variables can get unwieldy. We could create a .sqlcmdrc file in the user's profile that stores various settings which would be overridden by command line switches and environment variables.

Implement $() variable processing

Implement -i switch to process script files

Add history file support

The liner package supports a history file to remember line entries across app invocations.
We have a few options to discuss:

• Whether to hard code the history file location in the user profile or to allow the history file location to be a command line parameter or environment variable.
• How to control the file size

Input file path only supports forward slashes even on Windows

I am trying to use go-sqlcmd to execute a script (.SQL file). The command line I am using is below:

sqlcmd.exe -i "C:\somedir\somescript.sql" --authentication-method=ActiveDirectoryManagedIdentity -S someserver.database.windows.net

For some reason the sqlcmd exits silently without throwing any errors. I tried specifying non-existent file paths and the result is the same. I don't see any issues with -Q with the same authentication. Something seems specifically wrong with -i

@shueybubbles hinted to me to use forward slash / instead of \. That worked. Though, this being a Windows based environment, it would be natural to work with the Windows-style \. He also pointed out alecthomas/kong#198 for some related info.

go-sqlcmd throws error when multiple input files are specified. (-i)

When multiple valid input files are specified for -i, the go-sqlcmd throws unexpected argument error.

go-sqlcmd with single input file

sqlcmd-v0.8.0-windows-x64>sqlcmd.exe -i test.sql



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64)
        Apr 29 2022 18:00:13
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22000: ) (Hypervisor)


(1 row affected)

go-sqlcmd with multiple input files

sqlcmd-v0.8.0-windows-x64>sqlcmd.exe -i test.sql test.sql
sqlcmd.exe: error: unexpected argument test.sql

Output with ODBC based sqlcmd utility with multiple files

>sqlcmd -i test.sql test.sql



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64)
        Apr 29 2022 18:00:13
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22000: ) (Hypervisor)


(1 rows affected)



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64)
        Apr 29 2022 18:00:13
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22000: ) (Hypervisor)


(1 rows affected)

Workaround:
For now this can be worked around by specifying each inputfile with -i
E.g.

sqlcmd-v0.8.0-windows-x64>sqlcmd.exe -i test.sql -i test.sql



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64)
        Apr 29 2022 18:00:13
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22000: ) (Hypervisor)


(1 row affected)



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64)
        Apr 29 2022 18:00:13
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22000: ) (Hypervisor)


(1 row affected)

Another workaround is to specify multiple files by using ',' as separator

sqlcmd doesn't prompt for password when required

If the SQLCMDPASSWORD environment variable isn't set, and sqlcmd is running in interactive mode, it should prompt the user for the password if required.

Missing input file (-i) does not return a non-zero exit code

When sqlcmd -i is used, if doesn't exist, sqlcmd silently succeeds.

(Apoorv is going to look at this one)

Add syntax coloring for list command output

There are several SQL language syntax coloring packages available. We can use one to color the output of the :list command. The color scheme could be controlled by command line parameter or environment variable.

-b option is not working in go-sqlcmd

I´m trying to use the -b option in go-sqlcmd, and it always returns a rc:1 with NO stderr.

For recreating the scenario, I have this T-SQL sentence inside table_creation.sql:
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'ANSIBLE_DB_CICD' AND TYPE = 'U') BEGIN CREATE TABLE ANSIBLE_DB_CICD (ID INT IDENTITY(1,1), COMMENT VARCHAR (250)) END

In below image, you can see that I receive a rc: 1 if I´m using -b option, and I receive rc: 0 when I´m not using it, always with NO stderr:

I this CentOS VM where I have the go-sqlcmd installed, I also have the regular sqlcmd (the one that´s part of command-line tools). So, I did some testing with a "bad" script, testing both tools, and noticed that the behavior is different.

As you can see, when running the go-sqlcmd (/user/bin/sqlcmd) with -b option and a "bad" T-SQL, I receive rc:1 and nothing in the stderr. However, when doing the same with the regular sqlcmd (/opt/mssql-tools/bin/sqlcmd), I receive rc:1 but also some information about the error in the stderr:

Need scripts and pipeline for mac/linux/windows builds

We can defer automated github releases until later, but we need repeatable signed builds in ADO.

Errors for :R are thrown on stdout instead of stderr

When compared with the ODBC based sqlcmd utility, the go-sqlcmd utility throws error for :R and all other commands to stdout instead of stderr.
This issue is created to align go-sqlcmd behavior with ODBC based sqlcmd.

Usage on ODBC sqlcmd

>type test1.sql
:r missing.sql
go

>sqlcmd -i test1.sql >stdout.txt 2>stderr.txt
stdout: <Blank>
stderr: 'missing.sql': Invalid filename.

Usage on go-sqlcmd

sqlcmd-v0.8.0-windows-x64>sqlcmd.exe -i test.sql >stdout.txt 2>stderr.txt
stdout: open missing.sql: The system cannot find the file specified.
stderr: <blank>

shell behaves erratically after exiting go-sqlcmd

I have tried to use go-sqlcmd in Macbook Pro M1 Docker, but it doesn't seem to be working. My Dockerfile is based on Fedora 36. This is how I installed go-sqlcmd:

RUN curl -L https://github.com/microsoft/go-sqlcmd/releases/download/v0.7.0/sqlcmd-v0.7.0-linux-arm64.tar.bz2 > sqlcmd-v0.7.0-linux-arm64.tar.bz2
RUN tar jxf sqlcmd-v0.7.0-linux-arm64.tar.bz2 -C /usr/bin
RUN rm sqlcmd-v0.7.0-linux-arm64.tar.bz2

If I run the sqlcmd command without parameters in Docker container, it exits with the following error:

unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connect: connection refused

That's ok, but somehow the shell behaves erraneously after that.

If I run the sqlcmd with proper parameters, it doesn't seem to do anything. No errors, no output, but it certainly doesn't update database either. I use this kind of command:

/usr/bin/sqlcmd -S host.docker.internal -U <user> -d <database> -i <script>.sql  2>&1

I use the same command line with MacOS sqlcmd (original version, not go-sqlcmd) and it works just fine.

Is this a bug or I'm doing something wrong?

Add command line switches related to connection management

The relevant switches are:

• -a packet_size
• -l login_timeout
• -H workstation_name
• -K application_intent
• -N (encrypt connection)

Implement minimal sqlcmd command set for public preview

Reset
Exit
Setvar
List
Connect
ListVar

hierarchical JSON/XML BCP

for example a set of orders and orderdetails in a JSON file

would need to map between the levels that are being loaded (map detail line items to order headers)

The presentation of date/time data types needs some polishing

I would guess this is on a to-do-list, but I file it never the less.

The presentation of datetime values is a little funny as demonstrated by the screenshot below.

• For datetime, +00 is added if the fraction is 0.
• For datetime, no trailing zero is printed when last digit in fraction is 0.
• For date, hours and minutes are printed.
• For datetime2 a +0000 UTC is added that should not be there.
• For datetime2(3) .000 is not printed.
• For time a date of 0000-01-01 is added. Likewise a time zone which should be there is added.

Hangs when calling SP that uses a cursor

Any time I attempt to call a stored proc that iterates over a cursor, the tool hangs. Using this command:

sqlcmd -Q "exec TestSqlCmd" -S "DatabaseServer" -U DatabaseUser -d DatabaseName

and the following variations of the stored proc:

CREATE PROCEDURE [dbo].[TestSqlCmd]
AS
BEGIN
	DECLARE @tmp int;

	DECLARE Server_Cursor CURSOR FOR
	SELECT 1 UNION SELECT 1

	OPEN Server_Cursor;
	FETCH NEXT FROM Server_Cursor INTO @tmp;
	WHILE @@FETCH_STATUS = 0
		BEGIN
			FETCH NEXT FROM Server_Cursor INTO @tmp;
		END;
	CLOSE Server_Cursor;
	DEALLOCATE Server_Cursor;
END

GO

CREATE PROCEDURE [dbo].[TestSqlCmd]
AS
BEGIN
	DECLARE @tmp int;

	DECLARE Server_Cursor CURSOR FOR
	SELECT 1 UNION SELECT 1

	OPEN Server_Cursor;
	CLOSE Server_Cursor;
	DEALLOCATE Server_Cursor;
END

GO

The command hangs indefinitely. Strangely, if I remove the DEALLOCATE like so it works:

CREATE PROCEDURE [dbo].[TestSqlCmd]
AS
BEGIN
	DECLARE @tmp int;

	DECLARE Server_Cursor CURSOR FOR
	SELECT 1 UNION SELECT 1

	OPEN Server_Cursor;
	CLOSE Server_Cursor;
END

GO

but if I attempt doing so with the full SP it still hangs:

CREATE PROCEDURE [dbo].[TestSqlCmd]
AS
BEGIN
	DECLARE @tmp int;

	DECLARE Server_Cursor CURSOR FOR
	SELECT 1 UNION SELECT 1

	OPEN Server_Cursor;
	FETCH NEXT FROM Server_Cursor INTO @tmp;
	WHILE @@FETCH_STATUS = 0
		BEGIN
			FETCH NEXT FROM Server_Cursor INTO @tmp;
		END;
	CLOSE Server_Cursor;
END

GO

Every single one of the variations above work just fine using the standard sqlcmd tool. I've tested on a local SQL Server database and an Azure SQL Server database with same results, so it doesn't appear to be server-specific.

Sqlcmd doesn't validate custom batch separator

The ODBC sqlcmd disallows a batch separator that matches any value in this list. go-sqlcmd probably should do so.

BACKUP,BREAK,BROWSE,BULK,CHECKPOINT,CLUSTERED,COMMITTED,COMPUTE,CONFIRM,CONTROLROW,DATABASE,DBCC,DISK,DISTRIBUTED,DUMMY,ERRLVL,ERROREXIT,EXIT,FILE,FILLFACTOR,FLOPPY,HOLDLOCK,IDENTITY_INSERT,IDENTITYCOL,IF,KILL,LINENO,MERGE,MIRROREXIT,NONCLUSTERED,OFF,OFFSETS,ONCE,OVER,PERCENT,PERM,PERMANENT,PLAN,PRINT,PROC,PROCESSEXIT,RAISERROR,READ,READTEXT,RECONFIGURE,REPEATABLE,RESTORE,RETURN,ROWCOUNT,RULE,SAVE,SERIALIZABLE,SETUSER,SHUTDOWN,STATISTICS,TAPE,TEMP,TEXTSIZE,TOP,TRAN,TRIGGER,TRUNCATE,TSEQUEL,UNCOMMITTED,UPDATETEXT,USE,WAITFOR,WHILE,WRITETEXT

Sqlcmd could support some codepage options

We haven't yet implemented this switch:

-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]

The charmap package has a bunch of Windows code page decoders and encoders we could leverage to implement this switch.

make available in ADO/GH virtual environments

a persistent aka link and instructions to install with curl+tar is probably better than adding to images based on utilization

Add signing to Mac binaries

Will you be adding mac signing later on then?

Originally posted by @Charles-Gagnon in #10 (comment)

-F does not really work

sqlcmd -F="horiz"

results in

sqlcmd: error: --format must be one of "horiz","horizontal","vert","vertical" but got "=horiz"

It works with --format, though.

Bad alignment of result set.

The screenshot below says it all:

Extra blank lines in stdout (breaking change)

I am using sqlcmd to execute a script file which has 100 CREATE DATABASE statements, separated with a GO. A snippet of that file is provided at the end of this Issue. I observe that the console keeps scrolling upwards, presumably as a result of empty lines being returned to stdout.

Classic sqlcmd does not print any empty lines in this case. Hence, this is a breaking change for go-sqlcmd. Apart from a breaking change, it is a minor irritant when there is no real output expected from CR/D type statements like CREATE DATABASE.

Command line being used:

sqlcmd.exe -i "C:/somepath/100dbs.sql" --authentication-method=ActiveDirectoryManagedIdentity -S someserver.database.windows.net

Snippet from SQL script file being used:

create database [3dprinting.meta.stackexchange]
go

create database [3dprinting.stackexchange]
go


create database [academia.meta.stackexchange]
GO

create database [academia.stackexchange]
GO

create database [ai.meta.stackexchange]
GO

create database [ai.stackexchange]
GO

create database [android.meta.stackexchange]
GO

create database [android.stackexchange]
GO

create database [anime.meta.stackexchange]
GO

create database [anime.stackexchange]
GO

create database [apple.meta.stackexchange]
GO

... script truncated for brevity

sqlcmd app should be localized

We need an i8n implementation so user-visible strings can be localized. @stuartpa has some ideas for an implementation.

Windows console up/down arrow keys don't cycle through the input history

Something is preventing arrow key handling by the console host when running sqlcmd in interactive mode.
We use the rline package from usql, and usql has no bug like this.
We should consider replacing rline with direct usage of the readline package it wraps.

Pasting T/SQL into interactive console is very slow (and omits characters sometimes)

When I paste a multi-line query (for example [1]) into the interactive console using the ODBC version of sqlcmd, the paste action completes within .1 of a second.

When I paste the same multi-line query into the go-mssql interactive console it takes a few seconds. If I paste (as a single paste) the same query repeated 10 times into the the interactive console, it takes over a minute (and gets slower and slower).

Also, strangely, sometimes the two 'd' in the string 'Dedicated Admin Connection' are missed out. i.e. what is pasted in is 'Deicated Amin Connection', and the 'f' in the line 'from sys.endpoints as ep' is omitted.

[1]
select
CASE
WHEN es.session_id= @@spid THEN 'you are :)'
ELSE es.host_name
END AS Who_is_running_DAC,
es.original_login_name,
es.session_id,
es.login_time,
es.status
from sys.endpoints as ep
join sys.dm_exec_sessions es on
ep.endpoint_id=es.endpoint_id
where ep.name='Dedicated Admin Connection'

Update product build ADO pipeline to push zips/tars to github

Today our product build pipeline generates the various linux/windows binary archives but we have to manually upload them to the release. Let's add a service connection to github in our ADO and modify the build pipeline to push the binary archives to github automatically.

Evaluate stdout performance and experiment with buffered i/o for performance

Go stdout is not buffered. Performance of printing large result sets is likely to be poor. Measure the performance and look at buffered i/o options, whether it's implementing our own buffer like the native sqlcmd does or switching to the bufio package to wrap stdout.