sqlcmd

sqlcmd [-S server_name] [-U username] [-P password | -E | -G] [-d database_name]
[-Q "query" | -q "query" | -i input_file] [-o output_file]
[-V var=value] [-W] [-s separator] [other_options]

Description:
sqlcmd connects to SQL Server and executes T-SQL statements or scripts. Options like -S, -U, -P specify connection details, while -Q, -q, or -i define the T-SQL to execute. Results can be redirected using -o.

-S server_name
    Specifies the SQL Server instance to connect to. Can be hostname, IP, or named instance.

-d database_name
    Specifies the initial database to connect to upon login.

-U username
    Specifies the user name (login ID) to use for SQL Server Authentication.

-P password
    Specifies the password for the specified -U username.

-E
    Uses trusted connection (Windows Authentication). On Linux, this typically requires Kerberos configuration for domain authentication.

-G
    Uses Azure Active Directory (AAD) authentication. Essential for connecting to Azure SQL Database/Synapse Analytics with AAD identities.

-Q "query"
    Executes a query or stored procedure and then immediately exits sqlcmd.

-q "query"
    Executes a query or stored procedure, but remains at the sqlcmd prompt after execution.

-i input_file
    Specifies the input file containing T-SQL statements or sqlcmd commands to execute.

-o output_file
    Specifies the file to which all output from sqlcmd is written, including results, messages, and errors.

-l timeout
    Specifies the login timeout in seconds before sqlcmd times out when attempting to connect to a server.

-W
    Removes trailing spaces from query output. By default, trailing spaces are preserved for display.

-s col_separator
    Specifies the column separator character to use in the output. Default is a space.

-V variable=value
    Sets a sqlcmd scripting variable that can be referenced within scripts using $(VARIABLE).

-X
    Disables commands and variables that could compromise system security, enhancing safety for untrusted scripts.

sqlcmd is a powerful command-line utility designed for interacting with Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics instances. Available on Linux, it provides a robust interface for executing Transact-SQL (T-SQL) statements, stored procedures, and entire SQL scripts directly from the terminal.

This versatile tool is essential for database administrators and developers working in mixed-OS environments, enabling them to perform a wide array of tasks such as database administration, automated script deployments, data imports/exports, and ad-hoc querying. sqlcmd supports various authentication methods, including SQL Server Authentication and Azure Active Directory (AAD) authentication, making it adaptable to different security configurations.

Key features include the ability to define and use scripting variables, format output in different styles, and handle errors effectively, making it suitable for both interactive use and integration into shell scripts for automation. Its presence on Linux extends the reach of SQL Server management to a broader set of operating systems.

When using sqlcmd on Linux, note that some features available on Windows, such as the Dedicated Administrator Connection (DAC) or integrated Windows Authentication without Kerberos, may not be fully supported or behave identically.

Specifically, the -E option for trusted connections often requires a correctly configured Kerberos environment on the Linux machine to function.

For Azure SQL Database and Azure Synapse Analytics, the -G option for Azure Active Directory authentication is crucial and may require specific ODBC driver installations (e.g., `msodbcsql17`) and configurations.

Care should be taken with output formatting, especially when parsing results in scripts, as default spacing and headers can be verbose. For bulk data operations, tools like bcp might be more efficient.

sqlcmd supports scripting variables that can be defined using the -V option or the :setvar command in interactive mode or scripts. These variables are referenced within T-SQL scripts using the syntax $(VARIABLE_NAME). This feature is powerful for parameterizing scripts, allowing dynamic values for server names, database names, or other parameters without modifying the script file itself.

When run without the -Q, -q, or -i options, sqlcmd enters an interactive mode. In this mode, users can type T-SQL statements directly at the sqlcmd> prompt, execute them using the GO command, and view results. It supports various commands prefixed with a colon (e.g., :quit to exit, :help for assistance, :r to run a script). This mode is useful for ad-hoc queries, debugging, and interactive administration.

sqlcmd was introduced with SQL Server 2005, largely replacing its predecessor, osql, as the primary command-line utility for SQL Server. Its availability on Linux is a more recent development, driven by Microsoft's strategy to make SQL Server accessible across multiple platforms.

On Linux, sqlcmd is typically installed as part of the mssql-tools package, allowing administrators and developers to manage SQL Server instances, whether they run on Linux, Windows, or in cloud services like Azure, directly from their Linux terminals. This cross-platform availability significantly expanded its utility and adoption within diverse IT environments.

bcp(1), isql(1), mysql(1), psql(1)