MySQL Select Column Name Where Value

SQLCMD utility

Scope of application:SQL Server (all supported versions) Azure SQL database Azure SQL managed instance Azure Synapse Analytics Parallel data warehouse

For information on SQL Server 2014 and earlier versions, see sqlcmd utility.

For information about using sqlcmd on Linux, see Installing sqlcmd and bcp on Linux.

With the utility sqlcmd you can enter Transact-SQL statements, system procedures, and script files in several modes:

  • At a command prompt.
  • in the Query Editor in SQLCMD mode.
  • In a Windows script file.
  • In a job step of a SQL Server Agent job for an operating system (cmd.exe).

The utility uses ODBC to run Transact-SQL batches.

Download the latest version of the sqlcmd utility

Download Microsoft Command Line Utilities 15 for SQL Server (x64) (2.6 MB)
Download Microsoft Command Line Utilities 15 for SQL Server (x86) (2.3 MB)

The command line tools are generally available (GA version), but they are released with the SQL Server 2019 installation package (15.x).

Version information

Release number: 15.0.2
Build number: 15.0.2000.5
Release date: September 11, 2020

The new version of SQLCMD supports Azure AD authentication, including Multi-Factor Authentication (MFA) support for SQL Database, Azure Synapse Analytics and Always Encrypted features. The new BCP supports Azure AD authentication, including Multi-Factor Authentication (MFA) support for SQL Database and Azure Synapse Analytics.

System requirements: Windows 10, Windows 7, Windows 8, Windows 8.1, Windows Server 2008 - 2019

Both the integrated Windows Installer 5 and Microsoft ODBC Driver 17 for SQL Server 17 are required for this component.

To check the SQLCMD version, run the command and make sure it is version 15.0.2000.5 or higher.

Note

You need at least version 13.1 to support Always Encrypted () and Azure Active Directory authentication (). (You may have multiple versions of sqlcmd.exe installed on your computer. Make sure you are using the correct version. To determine which version, run.)

You can test the sqlcmd utility from the Azure Cloud Shell as it is pre-installed by default:

To execute sqlcmd statements in SSMS, select SQLCMD Mode from the navigation drop-down list at the top of the Query menu.

Important

SQL Server Management Studio (SSMS) uses the Microsoft .NET Framework SqlClient to run in both regular mode and SQLCMD mode of the Query Editor. When running sqlcmd used from the command line sqlcmd the ODBC driver. Because different default options may apply, running the same query in the SQLCMD mode of SQL Server Management Studio and in the utility sqlcmd possibly different results.

Current is for sqlcmd no space required between the command line option and the value. However, a space between the command line option and the value may be required in a future release.

Other topics:

syntax

Command line options

Registration processes

-A
Causes the login to SQL Server via a dedicated administrator connection (DAC). This type of connection is used to troubleshoot server problems. This connection only works with server computers that support DAC. If DAC is not available, generated sqlcmd an error message and then exits. For more information about DAC, see Diagnostic Connection for Database Administrators. The "-A" option is not supported with the "-G" option. To connect to SQL Database using the -A option, you must be a SQL Server Administrator. DAC is not available to Azure Active Directory administrators.

-C
This switch is used by the client to configure it to implicitly trust the server certificate without verification. This option corresponds to the ADO.NET option.

-dDatabase name
When starting sqlcmd a Database nameStatement. This option makes the sqlcmd -SQLCMDDBNAME script variable set. This parameter specifies the output database. The default value is the default database property of the login ID. If the database does not exist, an error message is generated and sqlcmd completed.

-D
Interprets the server name passed to -S as a DSN instead of a host name. For more information, see Connect to sqlcmd in the section DSN support in sqlcmd and bcp.

Note

The "-D" option is only available for Linux and MacOS clients. On Windows clients, it was previously used to denote a now obsolete option that has been removed and will be ignored.

-lLogin timeout
The number of seconds that can elapse in connecting to a server before a sqlcmd -Logon to the ODBC driver a timeout occurs. This option makes the sqlcmd -SQLCMDLOGINTIMEOUT script variable set. The default timeout value for a sqlcmd - Registration takes eight seconds. If you have the -G -Option to connect to SQL Database or Azure Synapse Analytics and authenticate using Azure Active Directory, a timeout value of at least 30 seconds is recommended. The login period time-out value must be a number between 0 and 65534. If the specified value is not a numeric value or is outside of this range, generated sqlcmd an error message. The value 0 defines an unlimited waiting time.

-E
Uses a trusted connection instead of a username and password to log in to SQL Server. By default, sqlcmd the trusted connection used when -E is not specified.

The option -E ignores possible environment variable settings for user names and passwords, e.g. SQLCMDPASSWORD. Will the option -E along with the option -U or the option -P used, an error message is generated.

-G
Sets “Column Encryption Setting” to. For more information, see Always Encrypted. Only pass keys that are stored in the Windows certificate store are supported. The -g switch requires at least sqlcmd Version 13.1. To determine the version, run.

-G
This option is used by the client when connecting to SQL Database or Azure Synapse Analytics to indicate that the user should be authenticated using Azure Active Directory authentication. This option makes the sqlcmd -Script variable "SQLCMDUSEAAD = true" set. The -G switch requires at least sqlcmd Version 13.1. To determine the version, run. For more information, see Connect to SQL Database or Azure Synapse Analytics using Azure Active Directory Authentication. The "-A" option is not supported with the "-G" option.

  • Azure Active Directory Username and Password:

    If you want to use an Azure Active Directory username and password, please select the option -G along with the username and password by selecting the options -U and -P provide.

    The "-G" parameter generates the following connection string in the backend:

  • Integrated Azure Active Directory authentication

    If you want to use Azure Active Directory Integrated Authentication, please select the option -G without username and password. Integrated AAD authentication requires Microsoft ODBC driver 17 for SQL Server 17.6.1 or later and a properly configured Kerberos environment.

    This will generate the following connection string in the back end:

    Note

    The -option (Trusted_Connection) cannot be used together with the -option.

  • Azure Active Directory Interactive

    Azure AD interactive authentication for Azure SQL Database and Azure Synapse Analytics allows you to use an interactive method that supports multi-factor authentication. For more information, see Active Directory Interactive Authentication.

    For interactive Azure AD authentication are sqlcmd in version 15.0.1000.34 or higher and ODBC in version 17.2 or higher required.

    To enable interactive authentication, specify the option “-G” with only the user name (-U) and without a password.

    The following example exports data using Azure AD interactive mode. A user name is specified, which represents an AAD account. This is the same example used in the previous section: Azure Active Directory username and password.

    In interactive mode, a password must be entered manually. For accounts with multi-factor authentication, you need to complete your configured MFA authentication method.

    The previous command generates the following connection string in the back end:

    In the event that an Azure AD user is also a federated user and is using a Windows account, the username required on the command line includes their domain account (example [email protected] below):

    The guest user alias is used when guest users exist in a specific Azure AD instance and belong to a group that exists in SQL Database and has database permissions to run the sqlcmd command (for example [email protected] ).

    Important

    There is a known problem with using the options and with SQLCMD: If the option is placed before the option, authentication may fail. Always use the option before the option.

-HWorkstation name
The name of a workstation. This option makes the sqlcmd -SQLCMDWORKSTATION script variable set. The name of the workstation is in the hostname Column of the sys.sysprocesses Catalog view and the name can be specified using the stored procedure sp_who be returned. If this option is not specified, the name of the current computer is assumed by default. This name can be used to mean various

sqlcmd- Identify sessions.

-j Displays unformatted error messages on the screen.

-KApplication intent
Declares the application workload type when connecting to a server. The only currently supported value is ReadOnly. If -K is not specified, the sqlcmd utility does not support connectivity to a secondary replica in an AlwaysOn availability group. For more information, see Active Secondary Replicas: Readable Secondary Replicas (Always On Availability Groups)

-MMulti-subnet failover
Always give -M when you connect to the availability group listener of a SQL Server availability group or a SQL Server failover cluster instance. -M ensures faster detection and connection with the (currently) active server. If -M is not specified, is -M deactivated. For more information, see Listeners, Client Connectivity, Application Failover, Availability Group Creation and Configuration (SQL Server), Failover Clustering and Always On Availability Groups (SQL Server), and Active Secondary: Readable Secondary Replicas (Always On Availability Groups).

-N
This switch is used by the client to request an encrypted connection.

-Ppassword
A user-supplied password. Passwords are case-sensitive. When the -U option is used but not the -P , and the SQLCMDPASSWORD environment variable has not been set, the user is being replaced by sqlcmd prompted for a password. The use of the NULL password is not recommended, but you can specify it by using a pair of contiguous double straight quotation marks at the top for the parameter value:

It is recommended to use a strong password.

Use a strong password!

The prompt for the password is displayed on the console as follows:

User input remains hidden, i.e. H. there is no display and the cursor remains at the starting position.

You can use the SQLCMDPASSWORD environment variable to set a default password for the current session. Because of this, it is not necessary to hard-code passwords in batch files.

The following example sets the SQLCMDPASSWORD variable first at the command prompt and then on the utility sqlcmd accessed. At the command prompt, enter:


At the prompt that appears, enter the following:

If the username and password combination is incorrect, an error message is generated.

NOTE! The OSQLPASSWORD environment variable has been retained for backward compatibility. The SQLCMDPASSWORD environment variable takes precedence over the OSQLPASSWORD environment variable. Since OSQLPASSWORD is no longer released, the auxiliary programs sqlcmd and osql can be used side by side without interference. Existing scripts will continue to work.

Will the option -P along with the option -E used, an error message is generated.

Be after the option -P If several arguments are given, an error message is generated and the program is terminated.

-S [protocol:]server[ \instance_name][ ,port]
Specifies the SQL Server instance to connect to. The option makes the sqlcmd -SQLCMDSERVER script variable set.

Give server_name to connect to the default instance of SQL Server on this server computer. Give server_name [ \instance_name ] to connect to the named instance of SQL Server on this server computer. If no server computer is specified, then sqlcmd connect to the default instance of SQL Server on the local computer. This option is required when sqlcmd run from a remote computer on the network.

protocol can be: tcp (TCP / IP), lpc (Shared memory) or np (Named Pipes).

If you server_name [ \instance_name ] when starting sqlcmd If not specified, SQL Server looks for and uses the SQLCMDSERVER environment variable.

Note

The OSQLSERVER environment variable has been retained for backward compatibility. The SQLCMDSERVER environment variable takes precedence over the OSQLSERVER environment variable. It means that sqlcmd and osql can be used in parallel without interference and that old scripts are still functional.

-ULogin ID
This is the login name or the username used for the stand-alone database. For stand-alone database users, you must specify the database name (-d) option.

Note

The OSQLUSER environment variable is available for backward compatibility. The SQLCMDUSER environment variable takes precedence over the OSQLUSER environment variable. This means that sqlcmd and osql can be used in parallel without interference. It also means that existing osql -Scripts continue to work.

If neither the option -U still the option -P is specified, tried sqlcmdto connect in Microsoft Windows authentication mode. Authentication is based on the Windows account of the user who is sqlcmd executes.

Will the option -U along with the option -E (described later in this article) generates an error message. Be after the option -U If several arguments are given, an error message is generated and the program is terminated.

-zNew password
Change Password:

-ZNew password
Change password and exit:

Input / output options
-fCode page | i:Code page[ ,O:Code page] | O:Code page[ , i:Code page]
Specifies the input and output code pages. The code page number is a numeric value that indicates an installed Windows code page.

Rules for converting code pages:

  • If no code pages are specified, used sqlcmd the current code page for both input and output files, unless the input file is a Unicode file, in which case no conversion is required.

  • sqlcmd automatically recognizes Unicode input files in big-endian or little-endian format. If the option -u is specified, the output is always Unicode files in little-endian format.

  • If no output file is specified, the output code page is the console code page. This allows the output to be properly displayed on the console.

  • If multiple input files are specified, they are assumed to use the same code page. Unicode and non-Unicode input files can be mixed.

At the command prompt, type chcp to check the code page of Cmd.exe.

-iInput file[ ,Input file 2...]
Identifies the file that contains a batch of SQL statements or stored procedures. You can specify multiple files to be read and processed in sequence. Do not use spaces between filenames. sqlcmd first checks whether all specified files are available. If one or more files are missing, sqlcmd completed. The -i and -Q / -q options are mutually exclusive.

Examples of paths:

File paths that contain spaces must be enclosed in quotation marks.

This option can be used multiple times: -iInput file-II input file.

-OOutput file
Identifies the file that is the output of the sqlcmd receives.

Is -u specified, the Output file saved in unicode format. If the file name is invalid, an error message is generated and sqlcmd completed. sqlcmd does not support multiple parallel write operations sqlcmd -Processes in the same file. In this case the file output would be damaged or faulty. Also, find out about the option -fwhich is also relevant for file formats. If it does not already exist, the file will be created. A file with the same name from a previous sqlcmd -Session will be overwritten. The file specified here is not the stdout -File. When a stdoutFile is specified, this file will not be used.

Examples of paths:

File paths that contain spaces must be enclosed in quotation marks.

-r[0 | 1]
Redirects the output of the error message to the screen (stderr). If you don't have a parameter or if you have 0 only error messages with severity level 11 or higher are redirected. If you 1 all error message output (including the output from PRINT) is redirected. This has no effect if you use the -o option. By default, messages are sent to stdout Posted.

-R
Causes sqlcmd numeric columns retrieved from SQL Server, as well as currency, date, and time columns localized based on the client's locale. By default, these columns are displayed according to the regional settings of the server.

-u
Indicates that Output file regardless of the format of Input file is saved in Unicode format.

Query execution options
-e
Writes input scripts to the standard output device (stdout).

-I
Sets the SET QUOTED_IDENTIFIER connection option to ON. The default setting is OFF. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

-q "Command line query"
Executes a query if sqlcmd starts, ends sqlcmd but not after the query has finished executing. You can run multiple queries separated by semicolons. Enclose the query in quotation marks, as shown in the following example.

At the command prompt, enter:

Important

Do not use the GO terminator in the query.

If -b is specified with this option sqlcmd terminated when an error occurred. The desk -b is described later in this article.

-Q "Command line query"
Executes a query if sqlcmd starts, and finishes sqlcmd immediately afterwards. You can run multiple queries separated by semicolons.

Enclose the query in quotation marks, as shown in the following example.

At the command prompt, enter:

Important

Do not use the GO terminator in the query.

If -b is specified with this option sqlcmd terminated when an error occurred. The desk -b is described later in this article.

-tQuery timeout
The number of seconds that elapse before a command (or SQL statement) times out. This option makes the sqlcmd -SQLCMDSTATTIMEOUT script variable set. If for Query timeout If no value is specified, the command will not time out. The value for querytime-out must be a number between 1 and 65534. If the specified value is not a numeric value or is outside of this range, generated sqlcmd an error message.

Note

The actual time-out value can be a few seconds from that for Time-out given value differ.

-vvar =value[ var =value...]
Creates a sqlcmdScript variable contained in a sqlcmd -Script can be used. Enclose the value in quotation marks if it contains spaces. You can have multiple values ​​for var= "values" specify. If one of the specified values ​​is incorrect, generated sqlcmd an error message and exits.

-x
Causes sqlcmd Script variables ignored. This parameter is useful when a script contains multiple INSERT statements that can contain strings in the same format as regular variables, such as $ (Variable name).

Formatting options
-HHeaders
Specifies the number of rows that are output between the column headings. By default, the headings are printed once for each result set of the query. This option makes the sqlcmd -SQLCMDHEADERS script variable set. With -1 you can specify that no headings are to be output. An invalid value causes sqlcmd an error message is generated and then exited.

-k [1 | 2]
Removes all control characters from the output, e.g. B. Tab characters and newline characters. With this parameter, column formatting is preserved when data is returned. If 1 is specified, the control characters are replaced by a single space. If 2 is specified, consecutive control characters are replaced by a single space. -k is the same as -k1.

-sColumn separator
Specifies the column separator. The default is a space. This option makes the sqlcmd -SQLCMDCOLSEP script variable set. To be able to use characters that have a special meaning for the operating system, e.g. For example, you must enclose the ampersand (&) or semicolon (;) in quotation marks ("). The column separator can be any 8-bit character.

-wColumn width
Specifies the screen width for the output. This option makes the sqlcmd -SQLCMDCOLWIDTH script variable set. The column width must be a number greater than 8 and less than 65536. If the specified column width is outside this range, generated sqlcmd an error message. The standard width is 80 characters. If an output line exceeds the specified column width, it is wrapped to the next line.

-W
This option removes trailing spaces from a column. Use this option in conjunction with the option -s to prepare data to be exported to another application. This option cannot be used with the -y or -Y be used.

-yDisplay_width_for_types_with_variable_length
This will make the sqlcmd -Script variable set. The default value is 256. It limits the number of characters that are returned for the large variable-length data types:

  • varchar (max)

  • nvarchar (max)

  • varbinary (max)

  • xml

  • UDT (user-defined data types)

  • text

  • ntext

  • image

Note

UDTs can have a fixed length depending on the implementation. If the length of a fixed-length UDT equals Display width falls below, this has no effect on the value of the returned UDT. If the length is equal to Display width however, the output will be cut off.

Important

Use the option -y 0 with extreme care as it can cause serious performance problems on the server and on the network, depending on the size of the data returned.

-YDisplay width for types with fixed length
This will make the sqlcmd -Script variable set. The default value is 0 (unlimited). It limits the number of characters returned for the following data types:

  • char (n) , where 1 <= n <= 8000

  • nchar (nn) , where 1 <= n <= 4000

  • varchar (nn) , where 1 <= n <= 8000

  • nvarchar (nn) , where 1 <= n <= 4000

  • varbinary (nn) , where 1 <= n <= 4000

  • variant

Error reporting options
-b
Indicates that sqlcmd terminates and returns a DOS ERRORLEVEL value if an error occurs. For the DOS ERRORLEVEL variable, the value 1 returned when the severity of the SQL Server error message is greater than 10. Otherwise, the value will be 0 returned. If the option -V in addition to -b has been set sqlcmd no error if the severity is less than that using -V established values. Command prompt batch files can be used to test the value of ERRORLEVEL and correct the error accordingly. sqlcmd does not report severity 10 errors (informational messages).

If that sqlcmd -Script contains an incorrect comment or a syntax error or a script variable is missing, the ERRORLEVEL value 1 is returned.

-mFailure level
Controls which error messages to stdout be sent. Error messages with a severity greater than or equal to this value are sent. If this value is on -1 is set, all messages are sent, including informational messages. There are no spaces between -m and -1 permissible. For example is -m-1 valid, -m -1 However not.

This option also enables the sqlcmd -SQLCMDERRORLEVEL script variable set. This variable has the default value 0.

-VError severity
Controls the severity level used to set the ERRORLEVEL variable. ERRORLEVEL is set for error messages with a severity level greater than or equal to this value. Values ​​less than 0 are returned as 0. Batch and CMD files can be used to test the value of the ERRORLEVEL variable.

Other options
-aPackage size
Requests a different size package. This option makes the sqlcmd -SQLCMDPACKETSIZE script variable set. packet_size must have a value between 512 and 32767. The default value is 4096. A larger value for the packet size can improve performance when executing scripts that have a large number of SQL statements between GO commands. You can request a larger package size. If the request is denied, used sqlcmd however, the server's default packet size value.

-cBatch terminator
Specifies the batch terminator. By default, commands are completed and sent to SQL Server by typing the word “GO” on a line of their own. If you reset the batch terminator, do not use any Transact-SQL reserved keywords or characters that have a special meaning for the operating system, even if the word or character is preceded by a backslash.

-L[c]
Lists the locally configured server computers and the names of the server computers that are sending broadcast messages over the network. This parameter cannot be used in conjunction with other parameters. A maximum of 3,000 server computers can be listed. If the server list was truncated due to the buffer size, a warning message is displayed.

Note

Due to the nature of broadcasting in networks, it is possible that sqlcmd does not receive a response from all servers in a timely manner. Therefore, the list of returned servers may vary with each call to this option.

If the optional parameter c is specified, the output will be without the header Server: displayed. Each server line is output without leading spaces. This representation is known as adjusted output. The simple output improves the processing power of scripting languages.

-p[1]
Reports performance statistics for each result set. The following output is an example of the format of performance statistics:

The following applies here:

= Number of transactions processed by SQL Server.

= Total duration of all transactions.

= Average duration of a single transaction.

= Average number of transactions per second.

All times are given in milliseconds.

If the optional parameter 1 If specified, the statistics are presented in a colon-separated format that can be easily imported into a spreadsheet or processed by a script.

If a value other than 1 specified, an error is generated and sqlcmd completed.

-X[1]
Disables commands that can compromise system security, if sqlcmd is executed from a batch file. The deactivated commands are recognized anyway; sqlcmd issues a warning and continues execution. If the optional parameter 1 is generated sqlcmd an error message and then exits. The following commands are disabled when the option -X is used:

If the -X -Option is specified, it prevents the passing of environment variables to sqlcmd. It also prevents the startup script specified using the SQLCMDINI script variable from running. More information about sqlcmd For script variables, see Using sqlcmd with script variables.

-?
Shows the version of sqlcmd and a syntax summary of the sqlcmd Options.

Remarks

The options do not need to be used in the order in which they are shown in the syntax section.

If multiple results are returned, there are sqlcmd select a blank line between each result set in a batch. In addition, the message will not be displayed if it does not apply to the statement being executed.

If you sqlcmd want to use interactively, enter sqlcmd at the command prompt and one or more of the options described earlier in this article. For more information, see Using the sqlcmd Utility.

Note

The options -L, -Q, -Z and -i cause that sqlcmd exits after execution.

The entire length of the sqlcmd-Command line in the command environment (Cmd.exe) including all arguments and extended variables corresponds to the length determined in the operating system for Cmd.exe.

Ranking of the variables (from lowest to highest)

  1. System-level environment variables

  2. User-level environment variables

  3. Before running sqlcmd command shell specified at the command prompt ( SET X = Y).

  4. sqlcmd-v X = Y

  5. : Setvar X Y

Note

Open the Control panel, click on system and then on the tab Extended to view the environment variables.

sqlcmd script variables

variableAssociated switchesR / Wdefault
SQLCMDUSER-UR.""
SQLCMDPASSWORD-P--""
SQLCMDSERVER-SR."DefaultLocalInstance"
SQLCMDWORKSTATION-HR."ComputerName"
SQLCMDDBNAME-dR.""
SQLCMDLOGINTIMEOUT-lR / W"8" (seconds)
SQLCMDSTATTIMEOUT-tR / W"0" = wait indefinitely
SQLCMDHEADERS-HR / W"0"
SQLCMDCOLSEP-SR / W" "
SQLCMDCOLWIDTH-wR / W"0"
SQLCMDPACKETSIZE-aR."4096"
SQLCMDERRORLEVEL-MR / W0
SQLCMDMAXVARTYPEWIDTH-yR / W"256"
SQLCMDMAXFIXEDTYPEWIDTH-yR / W"0" = unlimited
SQLCMDEDITORR / W"edit.com"
SQLCMDINIR.""
SQLCMDUSEAAD-GR / W""

SQLCMDUSER, SQLCMDPASSWORD, and SQLCMDSERVER are set when : Connect is used.

R indicates that the value can only be set once during program initialization.

R / W indicates that the value was set using the setvar Command can be changed and the new value is applied to subsequent commands.

sqlcmd commands

In addition to the Transact-SQL statements in sqlcmd the following commands are also available:

When using sqlcmd Commands the following:

  • With the exception of GO must be in front of everyone sqlcmd Commands must be given a colon (:).

    Important

    For backward compatibility with existing osql Scripts will recognize some of the commands even without specifying the colon, which are preceded by [ : ] are marked.

  • sqlcmd Commands are only recognized if they are at the beginning of a line.

  • With none sqlcmd Command is case-sensitive.

  • Each command must be on its own line. A command cannot be followed by a Transact-SQL statement or any other command.

  • The commands are executed immediately. They are not placed in the execution buffer like Transact-SQL statements.

Editing commands
[ : ] ED
Starts the text editor. This editor can be used to edit the current Transact-SQL batch or the last batch that was run. In order to process the last executed batch, the ED Command must be entered immediately after the last batch has finished executing.

The text editor is defined by the SQLCMDEDITOR environment variable. The standard editor is 'Edit'. You can change the editor by setting the SQLCMDEDITOR environment variable. For example, if you want to set Microsoft Notepad as your editor, you need to type the following at the command prompt:

[ : ] RESET
Clears the instruction cache.

: List
Outputs the content of the instruction cache.

variables
: Setvar <var> [ "value" ]
Are defined sqlcmd Script variables. Script variables have the following format:.

Variable names are case-insensitive.

Script variables can be set as follows:

  • Implicit - using a command line option. For example, the option -l the sqlcmd -Variable SQLCMDLOGINTIMEOUT set.

  • Explicitly - using the : Setvar Command.

  • By defining an environment variable before running the sqlcmd.

Note

The option X prevents environment variables from being set sqlcmd be handed over.

When a variable created using : Setvar has the same name as an environment variable is the one with : Setvar defined variable takes precedence.

Variable names cannot contain spaces.

Variable names cannot have the same form as a variable expression, e.g. B. $ (var).

If the script variable string value contains spaces, enclose the value in quotation marks. If no value is specified for a script variable, the script variable is omitted.

: Listvar
Displays the list of currently defined script variables.

Note

Only those script variables are displayed that are supported by sqlcmd or using the : Setvar Command have been set.

Output commands
: Error
<filename>| STDERR | STDOUT
Directs the entire error output to the with Filename specified file, in stderr or in stdout around. The command Error can be used multiple times in a script. By default, the error output is on stderr Posted.

file name
Creates and opens a file to which the output is written. If the file already exists, it will be truncated to 0 bytes. If the file cannot be accessed due to insufficient permissions or other reasons, the output is not redirected but sent to the last specified destination or to the default destination.

STDERR
Directs the error output to the stderr -Data stream. If this stream was redirected, the error output will be received from the destination to which the stream was redirected.

STDOUT
Directs the error output to the stdout -Data stream. If this data stream has been redirected, the error output will be received from the destination to which the data stream was redirected.

: Out <filename> | STDERR| STDOUT
Creates and routes all query results in and to the Filename specified file stderr or at stdout around. By default, the output is to stdout Posted. If the file already exists, it will be truncated to 0 bytes. The command Out can be used multiple times in a script.

: Perftrace <filename> | STDERR| STDOUT
Creates and forwards all performance tracking information in and to the Filename specified file stderr or stdout around. By default, the output is sent to performance tracking stdout Posted. If the file already exists, it will be truncated to 0 bytes. The command Perftrace can be used multiple times in a script.

Execution control commands
: On Error[ exit | ignore]
Determines the action to take if an error occurs during script or batch execution.

Will the option exit is used sqlcmd ended with the corresponding error value.

If the option ignore used is ignored sqlcmd clears the error and continues batch or script execution. An error message is issued by default.

[ : ] QUIT
Causes sqlcmd is terminated.

[ : ] EXIT[ (Instruction) ]
Allows you to use the result of a SELECT statement as a return value from sqlcmd to use. If numeric, the first column of the last result line is converted into a 4-byte long integer. MS-DOS, Linux, and Mac pass the low byte to the higher-level process or to the operating system's fault level. Windows 200x passes the entire 4-byte integer. The syntax is:

Example:

You can do that EXIT -Also include parameters as part of a batch file. For example, at the command prompt, type B. the following:

The utility sqlcmd sends all between the brackets () standing information to the server. When a system stored procedure selects a set and returns a value, only the selection is returned. One EXIT () -Instruction without specification between the brackets executes all previous statements in the batch and then exits the utility program without a return value.

If an incorrect query is given, sqlcmd exits without returning a value.

The following list contains a list of EXIT formats:

If the batch does not run, the utility exits immediately and does not return any value.

Runs the batch, then exits the utility and does not return a value.

Batches the query it contains, and then exits the utility after the results of the query are returned.

Becomes a RAISERROR in one sqlcmd Script is used and the status 127 is triggered sqlcmd terminated and the corresponding message ID returned to the client. Example:

This error causes the sqlcmd -Script terminates and message ID 50001 is returned to the client.

The return values ​​−1 to −99 are reserved for SQL Server. sqlcmd defines the following additional return values:

Return valuesDESCRIPTION
-100An error occurred before selecting the return value.
-101No rows were found when selecting a return value.
-102A conversion error occurred while selecting the return value.

GO [number]
GO signals both the end of a batch and the execution of all cached Transact-SQL statements. The batch is run multiple times as separate batches. You cannot declare a variable more than once in a single batch.

Other commands
: r <filename>
This adds additional Transact-SQL statements and sqlcmdCommands from the through <filename> The specified file is analyzed and the result is written to the instruction cache.

If the file contains Transact-SQL statements that are not used by the GO have followed, you must GO in the first line : r enter.

Note

<filename> is read relative to the start directory in which sqlcmd was executed.

The file is read and executed after a batch terminator is found. You can use the command : r use several times. The file can be any sqlcmd -Commands included. This closes the batch terminator GO a.

Note

The number of lines displayed in interactive mode is increased by 1 for each command found. The command appears in the output of the list command.

: Server list
Lists the locally configured servers and the names of the servers that send messages over the network.

: ConnectServer name[ \Instance name] [-l Time-out] [-U User name [-P password]]
Connects to an instance of SQL Server. Also closes the current connection.

Timeout options:

valuebehavior
0unlimited waiting time
n> 0Waiting time is n seconds

The SQLCMDSERVER script variable reflects the currently active connection.

If Time-out is not specified, the value of the SQLCMDLOGINTIMEOUT variable applies by default.

If only User name is specified (either as an option or as an environment variable), the user is prompted for a password. Users are not prompted when the SQLCMDUSER or SQLCMDPASSWORD environment variable is set. If no options or environment variables are specified, Windows authentication mode is used for logon. If z. For example, to connect to the instance of SQL Server using integrated security, enter the following command:

If you wanted to connect to the default instance on using script variables, you would type:

[ : ] !! < command>
Executes operating system commands. To run an operating system command, type two exclamation marks ( !! ) followed by the operating system command on a new line. Example:

Note

The command is run on the computer where the sqlcmd is performed.

: XML [ON | OFF]
For more information, see XML Output Format and JSON Output Format in this article.

: Help
Lists the sqlcmd Commands along with a brief description of each command.

sqlcmd filename

sqlcmd - Input files can be used with the option -i or the command : r can be specified. Output files can be created with the option -O or the commands : Error, :Out and : Perftrace can be specified. Here are some guidelines for using these files:

  • : Error, :Out and : Perftrace should be separate <filename> use. If the same <filename> is used, the entries in the files may be mixed up.

  • If an input file on a remote server contains a drive file path such as ": Out c: \ OutputFile.txt" and from sqlcmd is called on a local computer, the output file is created on the local computer and not on the remote server.

  • Valid file paths are, for example:, and. Use quotation marks when the path contains a space.

  • With each new one sqlcmd -Session, any existing files with the same name will be overwritten.

Information messages

sqlcmd outputs all informational messages sent by the server. The following example prints an informational message after the Transact-SQL statements are executed.

At the command prompt, enter the following command:

At the sqlcmd prompt, type:

When you press ENTER, you receive the following informational message: The database context has been changed to AdventureWorks2012.

Transact-SQL query output format

sqlcmd first outputs a column heading that contains the column names specified in the SELECT list. The column names are separated by the SQLCMDCOLSEP character. By default, this is a space. If the column name is shorter than the column width, the output is padded with spaces up to the next column.

This line is followed by a dividing line represented by a series of dashes. The following output shows an example.

start sqlcmd. Enter at the sqlcmd -Prompt for the following query:

When you press ENTER, the following result set is returned.

Although the column is only four characters wide, it has been expanded to accommodate the longer column name. By default, the output is ended with the 80th character. This can be changed by selecting the option -w or set the SQLCMDCOLWIDTH script variable.

XML output format

The XML output resulting from the FOR XML clause is output unformatted in a continuous data stream.

Use the command when you expect output in XML format.

Note

sqlcmd returns error messages in the usual format. Note that the error messages are also output in the XML text stream in XML format. With shows sqlcmd no information messages.

Use the following command to turn off XML mode:.

The GO command should not be used before the XML OFF command has been issued because XML OFF will cause sqlcmd returns to line-based output.

It is not possible to mix XML data (data stream) and rowset data. If the XML ON command was not used before a Transact-SQL statement that outputs XML data streams was executed, the output will not be rendered correctly. After using the XML ON command, you cannot execute Transact-SQL statements that return regular rowsets.

Note

The command does not support the SET STATISTICS XML statement.

JSON output format

Use the command when you expect output in JSON format. Otherwise, the output includes both the column name and the JSON text. This output is not a valid JSON code.

Use the following command to turn off XML mode:.

For more information, see XML output format in this article.

Use Azure Active Directory authentication

Examples of using Azure Active Directory authentication:

Best practices for using sqlcmd

The following methods have proven effective in optimizing security and efficiency.

  • Use Windows Integrated Security.

  • Use in automated environments -X [1] .

  • Secure input and output files using the appropriate NTFS file system permissions.

  • For performance reasons, do all of the tasks in one if possible sqlcmd -Session, not made up in a number of different sessions.

  • Set values ​​for batch or query timeouts that are longer than the expected execution time.

Use the following methods to maximize accuracy:

  • use -V16to log severity 16 messages. Severity 16 messages indicate general errors that the user can correct.

  • After the process has ended, check the exit code and the DOS ERRORLEVEL variable. sqlcmd usually returns 0 (zero). Otherwise, the ERRORLEVEL value becomes like that of -V configured set. In other words, don't expect ERRORLEVEL to match the error number reported by SQL Server. The error number is a SQL Server-specific value that corresponds to the system function @@ ERROR corresponds to. ERRORLEVEL is a SQLCMD-specific value that indicates why SQLCMD terminated and is detected by specifying the command line argument -b influenced.

The usage of -V16 in combination with checking the exit code and DOS ERRORLEVEL can help to detect errors in automated environments, especially quality gates before a production release.

Next Steps

Get help