The sqlcmd utility in SQL Server

The sqlcmd utility in SQL Server lets you run Transact-SQL statements and related commands at a command prompt, within a script file or in a Query Editor window in SQL Server Management Studio. It can be a handy tool, whether you want to run simple ad hoc queries or create script files to perform routine tasks or automate procedures. Although the utility supports a number of advanced options, you can start using sqlcmd with relative ease by entering simple commands at the Command Prompt window on any system where SQL Server is installed.

Interactive mode in sqlcmd
To quickly get started using sqlcmd you can enter interactive mode. Interactive mode lets you run Transact-SQL statements and sqlcmd commands directly, similar to entering statements in a Query Editor

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in December 2008

window in SQL Server Management Studio. To switch to interactive mode, enter the following command at the command prompt in the Command Prompt window:

sqlcmd

When you switch to interactive mode, the window name changes to SQLCMD and the command prompt displays the number 1, followed by a greater than (> ) symbol, as shown in Figure 1. The number 1 indicates that this is the first line of the statement. The lines are numbered consecutively until you run the statement, then the numbering starts over with 1.


Figure 1: Working with the sqlcmd utility in interactive mode
Click image for larger version

When you enter the sqlcmd command without specifying any options, the utility connects to the default instance of SQL Server on the local system and uses a trusted connection to the server. The default values, however, can be overridden. To connect to a specific instance of SQL Server when switching to interactive mode, you can enter a command like this:

sqlcmd -S server01\SqlSrv2008

In this case, the sqlcmd utility connects to the SQL Server instance SqlSrv2008 on the computer server01. Notice that the server/instance name is preceded by -S. This is one of the options supported by the utility. Option names are case sensitive, e.g., lowercase s (as in -s) is used to specify a column separator, not the name of a server.

The sqlcmd utility supports a number of options that are useful when connecting to SQL Server. You can specify the active database when you switch to interactive mode, for example, as shown in the following command:

sqlcmd -S server01\SqlSrv2008 -d AdventureWorks2008

The -d option indicates that the AdventureWorks2008 database should be the active database, although you can override this in interactive mode. For more details about any of the options mentioned here, as well as the other options supported by the sqlcmd utility, refer to the topic "sqlcmd utility" in SQL Server Books Online.

Using Transact-SQL statements in interactive mode
After you enter interactive mode, you can issue Transact-SQL statements against the server. You can run a USE command to specify the active database:

USE AdventureWorks
GO

The GO command follows on a separate line after the Transact-SQL statement. The USE statement is not executed until you enter the GO command. This way, you can write statements that span multiple lines. The statements are held in cache until you enter GO, at which time all lines (since the last GO) are processed. The results of running this command are shown in Figure 2.


Figure 2: Using the sqlcmd utility to run Transact-SQL statements in interactive mode
Click on image for larger version

As Figure 2 shows, the GO command is on line 2 because it is the second line in the group of statements. After you enter GO, the results of the statement (in this case, a message about the changed database) are displayed and the numbering switches back to 1. You can then enter a new set of statements.

Now that you've set the active database, you can issue a SELECT statement against that database, as shown in the following code:

SELECT LastName
FROM Sales.vSalesPerson
ORDER BY LastName
GO

The SELECT statement spans multiple lines and that the last line is, again, the GO command. When you enter GO, the entire statement is executed and the results are returned to the SQLCMD window, as shown in Figure 3.


Figure 3: Using the sqlcmd utility to retrieve data in interactive mode
Click on image for larger version

Running sqlcmd commands
In addition to running Transact-SQL statements in interactive mode, you can also run sqlcmd commands. The commands let you perform actions that cannot be performed by using Transact-SQL. For example, you can use sqlcmd commands to connect to a specific instance of SQL Server or to send query results to a text file. The following code demonstrates how you can use sqlcmd commands along with Transact-SQL in interactive mode:

:Connect server01\SqlSrv2008
:Out C:\Data\SqlCmdOutput.txt
USE AdventureWorks2008
GO
SELECT LastName
FROM Sales.vSalesPerson
ORDER BY LastName
GO
:EXIT

The first command is Connect, which is used to connect to a specific server and instance of SQL Server. Notice that the command is preceded by a colon. For some commands, the colon is optional -- it supports backward compatibility with the osql utility. I use the colon for all sqlcmd commands to provide consistency and to clearly delineate the commands from the Transact-SQL. In addition to preceding the command with a colon, each command must be the only command on a line.

The second command, Out, indicates that query output should be sent to the specified file. Because this command is specified, no query results or messages are displayed in the SQLCMD window, as shown in Figure 4.


Figure 4: Outputting query results to a text file in interactive mode
Click on image for larger version

Instead, all messages and query results are saved to the C:\Data\SqlCmdOutput.txt file. The following results show the content that has been saved to the file:

Changed database context to 'AdventureWorks2008'.

LastName
Abbas
Alberts
Ansman-Wolfe
Blythe
Campbell
Carson
Ito
Jiang
Mensa-Annan
Mitchell
Pak
Reiter
Saraiva
Tsoflias
Valdez
Vargas
Varkey Chudukatil

(17 rows affected)

As you can see, both the message about the database change and the query results have been saved to the file.

As handy as interactive mode can be in running ad hoc queries and commands, one of the biggest benefits of the sqlcmd utility is its ability to be run from a prompt in the Command Prompt window without switching to interactive mode. For example, you can run the following sqlcmd command statement:

sqlcmd -d AdventureWorks -Q "SELECT LastName FROM Sales.vSalesPerson"

The command uses the -d option to connect to the AdventureWorks database. By default, the command connects to the local default instance of SQL server and uses a trusted connection. The command also includes the -Q option, which lets you pass a query directly into a sqlcmd statement. In this case, the query is a simple SELECT statement.

This sqlcmd command statement looks similar to switching to interactive mode. Wwhenever you specify a query or an input file directly in the command statement, however, the command is executed without switching to interactive mode, and in this case, the query results are still returned to the Command Prompt window. You can also specify additional options in the command statement. The following statement connects to a specific instance of SQL Server, sets the active database, and issues a SELECT statement:

sqlcmd -S server01\SqlSrv2008 -d AdventureWorks2008 -Q "SELECT LastName FROM Sales.vSalesPerson ORDER BY LastName"

Once again, the statement will return the results to the Command Prompt window without switching to interactive mode. Although the command wraps to a second line, it is actually entered as a single command line and contains no line breaks. This applies to all example command statements shown here in which the statement wraps to multiple lines.

The sqlcmd utility also supports the -o option, which lets you send the query results to a file as you would in interactive mode. The following statement uses the -o option to output the results to the file C:\Data\SqlCmdOutput.txt:

sqlcmd -S server01\SqlSrv2008 -d AdventureWorks2008 -Q "SELECT LastName FROM Sales.vSalesPerson ORDER BY LastName" -o C:\Data\SqlCmdOutput.txt

You can also include options that affect the output. Here, I've included the -u option, which specifies that the output be stored in a Unicode format:

sqlcmd -S server01\SqlSrv2008 -d AdventureWorks2008 -Q "SELECT LastName FROM Sales.vSalesPerson ORDER BY LastName" -o C:\Data\SqlCmdOutput.txt –u

Running script files in sqlcmd
Another powerful feature of the sqlcmd utility is the ability to run script files. Suppose that you create the C:\Data\SqlCmdInput.sql file containing the following Transact-SQL statements:

USE AdventureWorks
GO
SELECT LastName
FROM Sales.vSalesPerson
ORDER BY LastName
GO

You can call the file from within the sqlcmd statement by using the -i option:

sqlcmd -i C:\Data\SqlCmdInput.sql -o C:\Data\SqlCmdOutput.txt –u

When you execute this command, the utility runs the statements in the SqlCmdInput.sql file and outputs the results to the SqlCmdOutput.txt file.

The ability to use input and output files lets you perform a variety of repetitive tasks simply by calling the source file and specifying the necessary options. You don't have to enter the same Transact-SQL statements each time you want to run the same query. The best part is that you can imbed sqlcmd command statements in .bat files or use them in SQL Server Agent jobs, allowing you to automate tasks and easily update procedures. The limit to such statements, however, is that they apply only to specific situations. The sqlcmd utility allows you to pass variable values into your Transact-SQL statements, so you can create more flexible script files.

So, suppose that the C:\Data\SqlCmdInput2.sql file contains the following Transact-SQL statements:

USE AdventureWorks
GO
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE CountryRegionName='$(Country)'
AND SalesYTD>$(Sales)
GO

The statement includes two variables: Country and Sales. When you reference the C:\Data\SqlCmdInput2.sql file in your sqlcmd statement, you can also include values for those variables:

sqlcmd -i C:\Data\SqlCmdInput2.sql -v Country="United States" -v Sales=5000000

The sqlcmd statement includes two instances of the -v option. The first one specifies that Country should equal "United States," and the second specifies that Sales should equal "5000000." As a result, only those salespeople in the United States whose year-to-date sales exceed 5000000 will be returned, as shown in the following results:

LastName SalesYTD
Mitchell 5200475.2313

Note that you can instead include multiple variable values in a single instance of the -v option:

sqlcmd -i C:\Data\SqlCmdInput2.sql -v Country="United States" Sales=5000000

Now, let's look at one other issue related to the sqlcmd utility -- SQLCMD mode in Management Studio. This mode lets you write and edit sqlcmd scripts within a Query Editor window. To switch to the SQLCMD mode, click SQLCMD Mode in the Query menu. You can then enter Transact-SQL statements and sqlcmd commands as you do in interactive mode:

:!!del C:\Data\SqlCmdOutput.txt
SELECT LastName, SalesYTD
FROM Sales.vSalesPerson
ORDER BY LastName
:Out C:\Data\SqlCmdOutput.txt

The first line begins with the sqlcmd command !!, followed by del. The !! command lets you run operating system commands, such as those you would use in the Command Prompt window. In this case, the !! command specifies the operating system del command, which is used to delete the C:\Data\SqlCmdOutput.txt file -- the sqlcmd commands are grayed over when displayed in the query window. A SELECT statement follows the del command. After the SELECT statement, an Out command outputs the query results to the C:\Data\SqlCmdOutput.txt file.

Using the SQLCMD mode in Management Studio can be a quick and easy way to take advantage of the sqlcmd commands, particularly the Out command. You can also take advantage of Management Studio features such as color-coding and Showplan. Many of the sqlcmd commands are not supported in SQLCMD mode, however, so be sure to check out the topic "Editing SQLCMD Scripts with Query Editor" in SQL Server Books Online. In the meantime, whether you use SQLCMD mode, interactive mode or command statements, you'll find the sqlcmd utility a useful tool for a variety of SQL Server-related tasks.

ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

Check out his blog: SQL Server with Mr. Denny.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top