SQL Server

SQL Server 2008 : The sqlcmd Command-Line Utility

10/17/2010 5:52:36 PM
The sqlcmd command-line utility is the next generation of the isql and osql utilities that you may have used in prior versions of SQL Server. It provides the same type of functionality as isql and osql, including the capability to connect to SQL Server from the command prompt and execute T-SQL commands. The T-SQL commands can be stored in a script file, entered interactively, or specified as command-line arguments to sqlcmd.


The isql and osql command-line utilities are not covered in this chapter. The isql utility was discontinued in SQL Server 2005 and is not supported in SQL Server 2008. The osql utility is still supported but will be removed in a future version of SQL Server. Make sure to use sqlcmd in place of osql to avoid unnecessary reworking in the future.

The syntax for sqlcmd follows:

[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -l login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format]]
[ -X [ 1 ] ] disable commands, startup script, environment variables [and exit]
[ -? show syntax summary ]

The number of options available for sqlcmd is extensive, but many of the options are not necessary for basic operations. To demonstrate the usefulness of this tool, we look at several different examples of the sqlcmd utility, from fairly simple (using few options) to more extensive.

Executing the sqlcmd Utility

Before we get into the examples, it is important to remember that sqlcmd can be run in several different ways. It can be run interactively from the command prompt, from a batch file, or from a Query Editor window in SSMS. When run interactively, the sqlcmd program name is entered at the command prompt with the required options to connect to the database server. When the connection is established, a numbered row is made available to enter the T-SQL commands. Multiple rows of T-SQL can be entered in a batch; they are executed only after the GO command has been entered. Figure 1 shows an example with two simple SELECTsqlcmd. The connection in this example was established by typing sqlcmd at the command prompt to establish a trusted connection to the default instance of SQL Server running on the machine on which the command prompt window is opened. statements that were executed interactively with

Figure 1. Executing sqlcmd interactively.

The capability to edit and execute sqlcmd scripts was added to SSMS with SQL Server 2005. A sqlcmd script can be opened or created in a Query Editor window within SSMS. To edit these scripts, you must place the editor in SQLCMD Mode. You do so by selecting Query, SQLCMD Mode or by clicking the related toolbar button. When the editor is put in SQLCMD Mode, it provides color coding and the capability to parse and execute the commands within the script. Figure 2 shows a sample sqlcmd script opened in SSMS in a Query Editor window set to SQLCMD Mode. The shaded lines are sqlcmd commands.

Figure 2. Executing and editing sqlcmd scripts in SSMS.

The most common means for executing sqlcmd utility is via a batch file. This method can provide a great deal of automation because it allows you to execute a script or many scripts by launching a single file. The examples shown in this section are geared toward the execution of sqlcmd in this manner. The following simple example illustrates the execution of sqlcmd, using a trusted connection to connect to the local database, and the execution of a simple query that is set using the –Q option:

sqlcmd -S (local) -E -Q"select getdate()"

You can expand this example by adding an output file to store the results of the query and add the –e option, which echoes the query that was run in the output results:

sqlcmd -S (local) -E -Q"select getdate()" -o c:\TestOutput.txt –e

The contents of the c:\TestOutput.txt file should look similar to this:

select getdate()
2008-09-10 20:29:05.645
(1 rows affected)

Using a trusted connection is not the only way to use sqlcmd to connect to a SQL Server instance. You can use the –U and –P command-line options to specify the SQL Server user and password. sqlcmd also provides an option to specify the password in an environmental variable named sqlcmdPASSWORD, which can be assigned prior to the sqlcmd execution and eliminates the need to hard-code the password in a batch file.

sqlcmd also provides a means for establishing a dedicated administrator connection (DAC) to the server. The DAC is typically used for troubleshooting on a server that is having problems. It allows an administrator to get onto the server when others may not be able to. If the DAC is enabled on the server, a connection can be established with the –A option and a query can be run, as shown in the following example:

sqlcmd -S (local) -A -Q"select getdate()"

If you need to manage more complex T-SQL execution, it is typically easier to store the T-SQL in a separate input file. The input file can then be referenced as a sqlcmd parameter. For example, say that you have the following T-SQL stored in a file named C:\TestsqlcmdInput.sql:

TO DISK = 'c:\master.bak'

TO DISK = 'c:\model.bak'

TO DISK = 'c:\msdb.bak'

The sqlcmd execution, which accepts the C:\TestsqlcmdInput.sql file as input and executes the commands within the file, looks like this:

sqlcmd -S (local) -E -i"C:\TestsqlcmdInput.sql" -o c:\TestOutput.txt –e

The execution of the preceding example backs up three of the system databases and writes the results to the output file specified.

Using Scripting Variables with sqlcmd

sqlcmd provides a means for utilizing variables within sqlcmd input files or scripts. These scripting variables can be assigned as sqlcmd parameters or set within the sqlcmd script. To illustrate the use of scripting variables, let’s change our previous backup example so that the database that will be backed up is a variable. A new input file named c:\BackupDatabase.sql should be created, and it should contain the following command:

BACKUP DATABASE $(DatabaseToBackup)
TO DISK = 'c:\$(DatabaseToBackup).bak'

The variable in the preceding example is named DatabaseToBackup. Scripting variables are referenced using the $( ) designators. These variables are resolved at the time of execution, and a simple replacement is performed. This allows variables to be specified within quotation marks, if necessary. The –v option is used to assign a value to a variable at the command prompt, as shown in the following example, which backs up the model database:

sqlcmd -S (local) -E -i"C:\BackupDatabase.sql" -v DatabaseToBackup = model

If multiple variables exist in the script, they can all be assigned after the –v parameter. These variables should not be separated by a delimiter, such as a comma or semicolon. Scripting variables can also be assigned within the script, using the :SETVAR command. The input file from the previous backup would be modified as follows to assign the DatabaseToBackup variable within the script:

:SETVAR DatabaseToBackup Model
BACKUP DATABASE $(DatabaseToBackup)
TO DISK = 'c:\$(DatabaseToBackup).bak'

Scripts that utilize variables, sqlcmd commands, and the many available options can be very sophisticated and can make your administrative life easier. The examples in this section illustrate some of the basic features of sqlcmd, including some of the features that go beyond what is available with osql.

Other -----------------
- Installing SQL Server 2008 Using a Configuration File
- SQL Server 2008 : Slipstream Installations
- SQL Server Programmability Objects
- SQL Server 2005 : Data Querying and Reporting (part 2)
- SQL Server 2005 : Data Querying and Reporting (part 1)
- Configuring SQL Server 2008 : Instances vs Default Instance
- sp_configure and SQL Server Management Studio
- Configuring SQL Server 2008 : Database Mail
- Configuring SQL Server 2008 : Full-Text Indexing
- SQL Server 2008 : Working with Indexes
- SQL Server 2008 : Working with Constraints
- SQL Server 2008 : Working with Tables and Views
- SQL Server 2008 : Viewing and Modifying Data (part 3) - Creating Functions and Creating Triggers
- SQL Server 2008 : Viewing and Modifying Data (part 2) - Creating Stored Procedures
- SQL Server 2008 : Viewing and Modifying Data (part 1) - Creating Views
Most View
- Designing and Optimizing Storage in an Exchange Server 2007 Environment (part 1) - When Is the Right Time to Implement NAS and SAN Devices?
- Active Directory Domain Services 2008 : Transfer the Infrastructure Master Role
- iPad SDK : Preparing Dudel for a New Tool (part 2) - Implementing Changes to the Controller Class
- Windows 7 : Deleting Unnecessary Files
- Programming WCF Services : The Response Service (part 3) - Queued Service-Side Programming & Response Service-Side Programming
- Windows 7 : Manage Your Network - Creating a Homegroup
- An OLAP Requirements Example: CompSales International (part 13) - Cube Perspectives
- Microsoft Dynamics AX 2009 : Working with Forms - Adding a Go to the Main Table Form link
- First Stages of SEO : Assessing Historical Progress
- Identity and Access Management : Cloud Service Provider IAM Practice
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS