Command Line Options

This section begins with an overview of the command line options and a brief introduction to the use of variables in SQLCMD. Specific options are then covered throughout the rest of the section.

Overview of Command Line Options

Any discussion of console applications such as SQLCMD invariably starts with examining command line options, i.e., its entry points. However, before covering the individual command line options of SQLCMD, it's a good idea to look at the help output of SQLCMD and highlight the options that existed in OSQL.

C:\>SQLCMD /? Microsoft (R) SQL Server Command Line Tool Version 9.00.1187.07 NT INTEL X86 Copyright (C) Microsoft Corporation.  All rights reserved. usage: SQLCMD             [-U login id]     [-P password]   [-S server]             [-H hostname]     [-E trusted connection]     [-d use database name]  [-l login timeout]     [-t query timeout]     [-h headers]            [-s colseparator]       [-w screen width]     [-a packetsize]         [-e echo input]          [-I Enable Quoted                                                        Identifiers]   [-c cmdend]            [-L[c] list servers[clean output]]   [-q "cmdline query"]   [-Q "cmdline query" and exit]   [-m errorlevel]        [-V severitylevel]   [-W remove trailing                                                   spaces]   [-u unicode output]    [-r[0|1] msgs to stderr]    [-i inputfile]         [-o outputfile]        [-z new password]   [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password                                                     and exit]   [-k[1|2] remove[replace] control characters]   [-y variable length type display width]   [-Y fixed length type display width]   [-p[1] print statistics[colon format]]   [-R use client regional setting]   [-b On error batch abort]   [-v var = "value"...] [-A dedicated admin connection]   [-X[1] disable commands, startup script, environment variables          [and exit]]   [-x disable variable substitution]   [-? show syntax summary] 

As you may notice, SQLCMD's options are case sensitive, so a letter may have a different meaning depending on its case. Also a forward slash (/) may be provided in place of dash (-) in front of the option.

In many cases, in addition to using a command line option, you can induce the same behavior by setting the environment or scripting variable. However, if provided, a command line option always overwrites a variable setting. As each individual option is described, the corresponding variable will also be listed if it exists.

Let's touch briefly on usage of variables in SQLCMD. Any environment variable of the command shell gets inherited by SQLCMD.exe as its child process and thus becomes available inside its session. At that point it is referred to as a "scripting variable."

To illustrate the usage of variables in SQLCMD we are going to manually set an environment variable DBNAME and then later use it inside a SQLCMD session as follows:

C:>SET DBNAME=pubs C:\>SQLCMD 1> use $(DBNAME) 2> go Changed database context to 'pubs'. 

In addition to directly using environment variables for scripting, scripting variables can also be defined explicitly inside a SQLCMD session. Later, you'll learn exactly how this is done.

Now rather than define scripting variables manually for each session as you saw in the previous example, you can set their values permanently via the Advanced System Properties tab in the control panel (see Figure 6-1).

Figure 6-1. Advanced System Properties Windows dialog.

When you click on the Environment Variables button the dialog shown in Figure 6-2 appears.

Figure 6-2. Environment Variables Windows dialog.

From this dialog you can set any environment variable for your specific user session and keep its value private and globally for all sessions, regardless of which user is logged on.

Scripting variables and their use will be discussed in depth later in this chapter. For now, the most important point to remember is that while using SQLCMD in the interactive mode you can always obtain a list of specific SQLCMD variables through the command :listvar. All previously defined environment variables and any new values obtained via either command line options or through the script are displayed. This is how you can check what option value the tool is currently using.

Connection Related Options

These options relate to the way SQLCMD establishes a connection to a SQL Server Database Engine Instance. Prior to establishing connection there might be a need to discover what servers are available on the network. This phase is also considered part of the connection options.

-L[c]

Suppose you want to list all locally configured servers and the ones broadcasting their names across the network. L is an option that can provide this information:

C:\>SQLCMD /L Servers:      MYSERVER      MYSERVER\INST1 

When -L is followed by lowercase c, you get clean output, without the Servers: string and without leading spaces before each server name, to simplify programmatic parsing of the output.

-S

This option can be used to specify to which SQL Server Database Engine instance SQLCMD should connect. If a server instance name is not specified, SQLCMD checks the value of the SQLCMDSERVER environment variable. If the environment variable is not defined, it tries to connect to the default instance of SQL Server running on the same computer.

-U and -P

To connect using SQL Authentication, you have to provide -U to enter the username and -P to enter the password. If the U option is not provided, SQLCMD checks for the SQLCMDUSER environment variable. If that variable is missing, SQLCMD checks for the OSQLUSER environment variable kept for backward compatibility purposes with the OSQL tool. Similarly, for the password, SQLCMD checks for SQLCMDPASSWORD and then OSQLPASSWORD if the P option is not present.

Tips and Tricks

If a username is provided without a password, SQLCMD prompts the user for a password before establishing a connection. You can use this trick to avoid revealing the password in your batch scripts.

-E

This option instructs the tool to connect to SQL Server Database Engine using Windows Integrated Security. If this option is used, it takes precedence over any environment variable definition mentioned in the previous section.

Tips and Tricks

If none of the environment variables specifying username have been set, you can omit this option and SQLCMD assumes you intend to use Windows Integrated Security as the default.

Here's how to connect to the default server instance running on the computer by explicitly instructing SQLCMD to use integrated security:

C:\>SQLCMD /E > 

The caret symbol (>) indicates that the connection succeeded and you are now ready to execute T-SQL and SQLCMD commands. At this point, you communicate with SQLCMD by typing in the console.

-A

Unlike a regular SQL Server connection, this connection option instructs SQLCMD to log in to the server using the dedicated administrator connection (DAC). The dedicated administrator connection runs on a special thread inside SQL Server and thus does not contend with other clients for resources. If DAC is not available (for example, if it is already being used) or the server does not support it, an error message is printed and SQLCMD immediately exits.

Tips and Tricks

DAC connection is new to SQL Server 2005, and earlier versions of SQL Server do not support it. Just because this option has been added to the tool does not imply earlier versions of SQL Server can be accessed this way.

Warning

You should use the DAC connection only as a last resort when you must access your server to perform emergency management operations, such as killing a runaway process, and the regular connection is not available.

-l and -t

Another important connection-related option, -l, specifies the login timeout in seconds. This argument must contain a numeric value in the range 0 through 65534, where 0 is used in place of infinite timeout. SQLCMD's default for this option is 8 seconds. Somewhat similar, the option t defines the statement timeout. The corresponding environment variables for these two options are SQLCMDLOGINTIMEOUT and SQLCMDSTATTIMETOUT.

-H

The H option enables you to specify a workstation name. You can use this to distinguish different SQLCMD sessions from each other when executing the sp_who stored procedure or during Profiler tracing. This option corresponds to the environment variable SQLCMDWORKSTATION.

-d

The initial database name option d may be used instead of the USE <dbname> statement. This option is most useful when you need to submit a T-SQL query for batch execution in a specific database context. This option corresponds to an environment variable named SQLCMDDBNAME.

-q and -Q

To submit a specific command for immediate execution, you would use q or Q, depending on the intent. Q instructs SQLCMD to exit when the command line terminates, whereas q leaves SQLCMD in interactive mode. An example follows:

C:\>SQLCMD /E /q"select * from HumanResources.Department"/dAdventureWorks 

Tips and Tricks

Keep in mind that you cannot submit multiple batches as part of one command. SQLCMD does not recognize batch separators when processing commands for immediate execution.

-a

Option a is used to specify a packet size in the range of 512 through 32767. It has no visible effect on the output. Increased packet size can enhance performance of a large script execution by packing more data in the envelope. However, the provider can choose not to grant this request, in which case SQLCMD defaults to the server default packet size.

This option corresponds to an environment variable SQLCMDPACKETSIZE.

-I

Option I sets the QUOTED_IDENTIFIER setting for the connection to ON. The default setting for this option is off. When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks.

Formatting Options

These options relate to the way SQLCMD processes and formats query results. Multiple control points are available to the user concerning how data is going to be displayed or stored in the output file, and they are covered in this section.

-h

This option controls the number of rows printed between column headings. This results in the entire resultset being split into batches of the size specified in the h setting, with each one preceded by its own set of headings. A default value of 0 instructs SQLCMD not to split the resultset.

In this example compare the default output

C:\>SQLCMD /E 1> select top(3) name from sys.objects 2> go name ------------------------------------------------------ sysrowsetcolumns sysrowsets sysallocunits 

with the following output obtained when the value 1 was supplied for this setting, essentially meaning split the results into batches of one row each.

C:\>SQLCMD /E /h1 1> select top(3) name from sys.objects 2> go name ------------------------------------------------------ sysrowsetcolumns name ------------------------------------------------------ sysrowsets name ------------------------------------------------------ sysallocunits 

This setting corresponds to the environment variable SQLCMDHEADERS.

Tips and Tricks

Use a value of -1 to specify that no headers should be printed. If -1 is supplied, there must be no space between the parameter and the setting, that is, -h-1. Otherwise, SQLCMD interprets it as a separate option and fails.

-s

Option s provides a different default for the column separator in place of blank space. To use characters that have special meaning to the operating system, enclose the character in double quotation marks. This option corresponds to the environment variable SQLCMDCOLSEP.

Tips and Tricks

If you don't want to have any separation between columns, you can pass in an empty string as follows s"".

-w

Option w specifies screen width. The default value is 80. When the printed output reaches the specified value, it wraps to a new line. This option corresponds to the environment variable SQLCMDCOLWIDTH.

-e

Option e causes contents of the script file (i.e., queries) to be written to stdout in addition to the regular output.

Tips and Tricks

This option may be useful for troubleshooting when a script is produced from other files being read with the :r command or from scripting variables that are expanded inside a SQLCMD session.

-W

This option strips spaces from the ends of the columns, causing all columns of each row to be printed next to each other without regard for column justification.

Tips and Tricks

This technique is especially useful when preparing data for import into another program in combination with option -s and -h.

Suppose you need to create a comma-separated file of query results for subsequent import into a spreadsheet application. Assuming the query is stored in the in.sql script file, you can produce the desired output as follows.

C:\>SQLCMD -i in.sql -s, -W -h-1 -o out.csv 

Warning

You cannot combine this option with options -y or -Y; they are mutually exclusive.

-y <column width >

This option limits the number of characters that are printed for large variable-length types. These types are varchar(max), nvarchar(max), varbinary(max), XML, user-defined types, ntext, text, and image. If the returned column data is shorter than the specified display width, the output is padded up to that limit. If 0 is specified, the column output is returned without any truncation. This option corresponds to environment variable SQLCMDMAXVARTYPEWIDTH.

Tips and Tricks

In reality, SQLCMD allows a fine level of control for this option only in the range of 1 through 8000. After that, any value is treated as if 0 was provided.

Warning

Keep in mind that even if 0 is supplied, the SQLCMD output is limited to a sufficiently large but finite number of characters for performance reasons, so some data truncation is still possible.

-Y <column width>

This option controls truncation of fixed-width character data types. These types include char(1...8000), nchar(1...4000), varchar(1...8000), nvarchar(1...4000), and variant. This option corresponds to environment variable SQLCMDMAXFIXEDTYPEWIDTH. The default value for this option is 0, which means there will be no truncation or unlimited column width.

-k [1|2]

This option causes any control characters, such as tab (\t), newline (\n), and others, to be removed from the output. This allows column formatting to be preserved when requested column data contains special characters. Possible values for this option include 1 and 2. A value of 1 instructs SQLCMD to replace control characters with a single space. A value of 2 instructs SQLCMD tool to apply a single space to any sequence of special characters. For example, when supplying k2, the following sequence

\t\t\t\r\n 

is automatically replaced with a single blank space ( ) if it is encountered in the script.

Command Execution Options

Command execution options relate to the way SQLCMD executes queries and treats errors if they occur. Sometimes users also might want to record query execution statistics.

-c

By default, commands are terminated and sent to SQL Server Database Engine if GO is entered on a line by itself. The default batch separator can be changed with the c option.

Tips and Tricks

When you reset the batch terminator, do not use Transact-SQL reserved words or characters that have special meaning to the operating system, whether preceded by a backslash or not, even though this was allowed by legacy.

-m error_level

The error level m option customizes display of the error messages. Any error higher than the specified number gets reported, along with its message number, state, and error level. A value of -1 specifies that all headers are returned with messages, even informational messages. This option corresponds to environment variable SQLCMDERRORLEVEL.

p [1]

This option enables you to print performance statistics. An example follows:

Network packet size (bytes): 4096 2 xact[s]: Clock Time (ms.): total 321 avg 321.00 (3.12 xacts per sec.) 

As you can see, there was one total transaction processed by SQL Server Database Engine. It took 321 milliseconds to execute, with the same average time per transaction, enabling the server to run 3.12 transactions of this type per second.

Note that performance statistics are printed for every resultset. If optional argument 1 is provided, statistics output is printed in a colonseparated format that can be easily imported into a spreadsheet, or processed by a script.

For the previous example the results would look like this:

4096:1:321:321.00:3.12 

Tips and Tricks

Performance statistics information comes directly from SQLCMD and does not add any additional burden on SQL Server Database Engine Query Processor, so it is safe to use even under heavy server load, which is when it becomes most useful. SQLCMD uses OLE-DB technology under the covers to get the statistics, but it does not do any calculations internally.

File Stream and Redirection Options

Unless you want to simply connect to the server and execute ad-hoc T-SQL queries, you are going to start developing scripts and run them together. SQLCMD enables you to specify input and output files during execution specifically for this purpose.

-i input_file[,file2...]

This option enables you to supply one or several script files for immediate execution. When multiple files are specified, they are processed in the same order as they are specified on the command line. File names should not be separated by spaces. If the file name contains embedded spaces, the file name needs to be enclosed within double quotes.

For example:

C:\sqlscripts>SQLCMD -i"file 1.sql","file 2.sql" 

SQLCMD first checks whether any of the files are missing before attempting to execute them.

-o output_file

This option enables you to specify an output file for SQLCMD session execution.

Tips and Tricks

If you combine the output file option with the u switch, the output file is saved in Unicode format.

-r [0|1]

This option redirects message output, also referred to as stderr, to the screen. If you specify 0, only error messages with a severity level of 17 or higher are redirected. This is also the default option. If you specify 1, all message output is redirected.

Error Handling and Reporting Options

Error handling and reporting is an important part of any program. SQLCMD enables you to control how errors affect script execution based on their severity.

-V <severity level>

This option specifies the minimal severity level that will be reported by SQLCMD. If a T-SQL script reports a lower severity error than the severity level provided with V option, 0 is reported; otherwise, the severity level is returned.

-b

This option makes SQLCMD exit upon error. At the same time SQLCMD sets the value of environment variable ERRORLEVEL.

When SQL Server Database Engine reports an error of severity greater than 10, SQLCMD sets the ERRORLEVEL value to 1; otherwise, it sets it to 0. If a SQLCMD script contains a bad instruction, the ERRORLEVEL value is set to 1 as well.

Most operating system batch files can test the value of this environment variable and handle the error appropriately.

Let's first create a script file c:\sqlscripts\myscript.sql containing the following code:

select 1 go -- we expect for this batch to fail select * from nonexistent_table go select 2 go 

At this point we are going to create an operating system batch script run_script.cmd that will invoke the SQLCMD tool by passing script file in as an argument and examine the ERRORLEVEL value after the tool returns.

sqlcmd /i"c:\sqlscripts\myscript.sql" echo %ERRORLEVEL% 

Running batch script produces the following output:

C:\sqlscripts>run_script.cmd C:\sqlscripts>sqlcmd /i"c:\sqlscripts\myscript.sql" ---------------------                     1 (1 rows affected) Msg 208, Level 16, State 1, Server KOMODO_Q5, Line 2 Invalid object name 'nonexistent_table'. ---------------------                     2 (1 rows affected) C:\sqlscripts>echo 0 0 

If we now modify the batch script to use option -b, the result is different.

C:\sqlscripts>run_script.cmd C:\sqlscripts>sqlcmd /i"c:\sqlscripts\myscript.sql" /b ---------------------                     1 (1 rows affected) Msg 208, Level 16, State 1, Server KOMODO_Q5, Line 2 Invalid object name 'nonexistent_table'. C:\sqlscripts>echo 1 1 

Two things in this example are worth highlighting. First, the T-SQL script gets aborted after the second batch that contains an invalid statement. Second, the ERRORLEVEL environment variable indicates there was a problem running SQLCMD session and the batch script can examine its value to make subsequent logical decisions.

Tips and Tricks

If the V option is used along with the b option, errors are not reported when the severity level raised is lower than the severity level specified with V.

Language-Specific Conversion Options

These options direct SQLCMD how to treat input and how to format output so it is interpreted correctly in non-English speaking countries.

-R

This option directs SQLCMD to use client regional settings when converting currency, date, and time data to character data. By default server side settings are used.

-u

This option instructs the tool to store the output file in Unicode format, regardless of the format of the input script file.

-f <codepage> [i:<codepage>][,o:<codepage>]

The main option, -f, drives codepage specification. Before you dive into how to specify a codepage, it's important to understand what it is. A codepage defines a character set, which can include numbers, punctuation marks, and other glyphs specific to each language. Naturally, codepages are not the same for each language. Some languages, such as Japanese and Hindi, have multi-byte characters, whereas others, such as English and German, need only one byte to represent each character. Therefore, you would only use option -f and define a codepage by its specific numeric value, if you were dealing with multiple codepages within your environment and needed to either resolve the ambiguity or simply force the output in a certain way.

When used by itself, option -f is applied to both the input and output codepage. Optionally, the user can supply separate parameters to drive input codepage (i:) or output codepage (o:). If an input codepage is not provided, SQLCMD tries to use the current codepage to interpret the input file. If the input file is already in Unicode format, no conversion is needed. Similarly, if an output codepage is not provided, SQLCMD uses the console codepage for printing data to display.

Warning

Do not supply output codepage specifications when using the -u option. Codepage specifications conflict with the Unicode format.

Tips and Tricks

When supplying multiple input files, they are assumed to be of the same codepage; however, Unicode and non-Unicode files can safely be mixed.

You can understand conversion specification better if you look at a few examples.

This example prescribes the use of Ansi Latin 1 as the codepage for an output file:

SQLCMD i in.sql o out.txt f o:1252 

This example instructs SQLCMD to use Ansi Latin 1 as the codepage for an input file and to provide output in Unicode format:

SQLCMD i in.sql o out.txt f 1252 u 

This command tells SQLCMD to use Ansi Latin 1 as the input file codepage and to produce an output file using an ISO-8859-1 codepage:

SQLCMD i in.sql o out.txt f i:1252,o:28591 

Security and Scripting Options

Two new command line options in SQLCMD provide a certain degree of protection when you are running untrusted scripts: -x and -X. This section also looks at the v option.

-x

This option causes SQLCMD to ignore scripting variables completely. Keep in mind while using the -x flag that all variable-related commands or command line options are still allowed, for example, :setvar or -v, except that the variable substitution will not take place.

Tips and Tricks

This option might also be useful when your script contains textual strings that have the same pattern as SQLCMD variables.

Take a look at the following example:

INSERT INTO mytable VALUES ('Hourly rate will be calculated as $(price)/number of hours') 

-X [1]

This option disables commands that may potentially compromise system security when SQLCMD is executed from a batch file. They include ED and !! < command> and are described in detail later in this chapter. These commands are still recognized by the tool, and SQLCMD issues a warning message and continues on unless option argument 1 is provided. In such case, SQLCMD issues an error message and exits.

Additionally, environment variables are not passed on to SQLCMD, and, subsequently, the startup script specified with SQLCMDINI environment variable is not executed.

Please note that the -X option provides a different aspect of protection than -x listed, so both options may be combined if desired.

-v <variable name>="<variable value>"

This option enables you to define one or more variables right on the command line before running a script. The variable value needs to be enclosed in double quotes only if it contains blank spaces. This option is discussed later in this chapter when it covers different scripting usage scenarios. Multiple variables can be supplied this way, separated by a blank space.