The sqlcmd utility in SQL Server is a command-line tool that lets you submit T-SQL statements or batches to local and remote instances of SQL Server. The utility is extremely useful for repetitive database tasks such as batch processing or unit testing. It also provides an easy way of simulating load to a database under development.

To help you develop and test sqlcmd scripts, the query window in SQL Server Management Studio (SSMS) supports SQLCMD Mode, which lets you run most sqlcmd ‘colon’ directives as well as T-SQL statements. You can submit commands from within Windows script files (such as .bat), via sp_cmdshell, or through SQL Server Agent CmdExec jobs. You can also run sqlcmd within PowerShell. In addition, PowerShell includes, via SQLPS, a version of sqlcmd called invoke-sqlcmd that runs many, but not all, of the directives. This has the advantage of returning results as PowerShell objects.

In this article, I introduce you to the basics of the sqlcmd utility and discuss how to create scripts from within SSMS and at a Windows command prompt and how to run or schedule these scripts. Once you know how to use the utility within these environments, you’ll be able to create commands that you can add to your Windows script files, SQL Server Agent jobs, and PowerShell SQLPS applications.

The article includes a number of examples that demonstrate how to use sqlcmd. I developed these examples against a local instance of SQL Server 2012, with the AdventureWorks2012 database installed. However, you can use sqlcmd with any accessible SQL Server instance, from version 2005 on, and against any accessible database. Simply substitute the connection and database information in the examples, as appropriate, and you should have no problem following along.

Running sqlcmd in SQL Server Management Studio

If you’re already using SSMS regularly, as are most SQL Server developers and DBAs, you’ll find the SSMS query window a good place to start learning about sqlcmd. To run sqlcmd statements, you must change the query window to SQLCMD Mode by clicking the SQLCMD Mode option on the Query menu.

SSMS in SQLCMD Mode provides a handy environment for creating and testing your sqlcmd statements. Editing commands, for example, is much easier in SSMS than at a Windows command prompt. To get started in SSMS, connect to a SQL Server instance, open a query window, and switch to SQLCMD Mode. Then start typing.

When working in SQLCMD Mode, you can enter two types of statements: sqlcmd directives and T-SQL statements. The sqlcmd directives are a set of special commands you can use in conjunction with the T-SQL statements. Let’s look at an example to better understand how they work. The following set of statements delete the PersonData.txt file if it exists, connects to a local instance of SQL Server, retrieves data from the Person table in the AdventureWorks2012 database, and saves the output to a new copy of the PersonData.txt file:

We’ll go through this code line-by-line shortly, but first take a look at Figure 1. Notice that the sqlcmd directives are automatically highlighted in gray when you work in SQLCMD Mode. The T-SQL statements appear as they would in regular query mode.

Figure 1: Running command in SQLCMD Mode in SSMS

As you can see in Figure 1, a colon (:) precedes the sqlcmd directives. The colon is required for most of these commands. For a few of them, however, such as quit and exit, you can omit the colon. (This was done to support backward compatibility with the osql utility.) For details about this and other command-related issues, see the topic “sqlcmd Utility” in SQL Server Books Online. In the meantime, let’s look at the commands used in our example. The first one begins with double exclamation points (!!):

The exclamation points let you run a Windows operating system command, such as those you’d run directly at a command prompt. In this case, the command first uses if exists to verify the existence of the PersonData.txt file and then uses del to remove the file if it exists. (Note that each sqlcmd directive must run on its own line.)

The second line in our example uses the connect command to specify a connection to a SQL Server instance:

Although this example is specific to a local SQL Server instance, you can connect to any available instance for which you’ve been granted access. If that instance is the default one, you need only provide the server name. In addition, when you specify the server and, optionally, the instance name, sqlcmd uses Windows authentication to connect to SQL Server with your current account. However, you can also specify a username and password when connecting to an instance. To specify a username, add the -U switch followed by the username. To include a password, add the -P switch followed by the password. If you enter the username option without the password option, you’ll be prompted for a password.

The next sqlcmd directive in our example is out:

The command specifies that all query results should be redirected to an output file, in this case, the PersonData.txt file. If the file already exists when you run this commend, the file will be truncated. For this reason, you might not need to run a Windows command to first delete the file, as we do in the first line. It all depends on your specific needs.

Next in our example are the T-SQL statements that change the database context to AdventureWorks2012 and retrieve data from the Person table:

The two statements are both straightforward T-SQL. You simply include them along with your sqlcmd directives. When you run a block of code such as the one in our example, your commands and T-SQL statements are executed in the specified order. For this reason, after the connect command runs, you’ll see the following results in the output pane of the query window:

However, after the out command runs, query output is saved to the PersonData.txt file. The following results show what you’ll find in the file:

Of course, you don’t have to redirect your output to a file, but if you don’t, the results will appear in the output pane of the query window.

Running sqlcmd in Interactive Mode

As mentioned earlier, using SQLCMD Mode in SSMS can be useful for developing and testing your sqlcmd code. However, you can then run your commands at a Windows command prompt by using sqlcmd in interactive mode. To work in interactive mode, open a command prompt window and enter sqlcmd at the command prompt. The prompt’s drive letter will be replaced by a 1, which represents the first line where you start entering your commands. The lines are numbered sequentially until all the commands are submitted, and then the numbering starts again.

When you use sqlcmd to switch to interactive mode, the utility establishes a connection to the default instance of SQL Server. If your system includes only named instances or you want to connect to a specific instance, you must specify the instance by adding the –S switch and instance name, as shown in the following command:

In this case, I’ve used a period to represent the local computer, and I’m connecting to an instance named SqlSrv2012. But you can connect to any accessible instance.

Once you’ve entered interactive mode, you can type in your T-SQL statements. To add a line break after a statement or part of a statement, press Enter. The sqlcmd utility does not submit your T-SQL statements to SQL Server until you run the GO command. For example, suppose you enter the following statements at the sqlcmd command prompt:

The USE statement will change the database context to the AdventureWorks2012 database, and the SELECT statement will retrieve data from the database. However, these statements are not submitted to SQL Server until you type GO and then press Enter. The utility then submits the statements and returns the results to the command prompt window.

Figure 2 shows what the command prompt window looked like on my system after I switched to interactive mode and submitted the statements. Notice that with each line of code, the line numbers are incremented, but after the GO command runs, the command prompt returns to 1.

Figure 2: Running a sqlcmd query in interactive mode

In the examples we’ve looked at so far, our T-SQL has consisted primarily of data manipulation language (DML) statements. However, you’re not limited to DML when issuing T-SQL statements. You can run data definition language (DDL) statements, execute stored procedures and perform database maintenance. For example, you can just as easily run the following SELECT...INTO statement:

Once again, you enter each line and then submit the statements by using GO. Figure 3 shows what the command prompt window looked like on my system after adding the next set of commands.

Figure 3: Using sqlcmd in interactive mode to create a table

In this case, I typed nothing at the first line and simply pressed Enter to add space to my commands. Then I typed the T-SQL statements and entered GO. The results show that I changed the database context and that 273 rows were affected. If I had wanted, I could have then run a query against the new table to verify that it had been created correctly.

When using sqlcmd in interactive mode, you can also include sqlcmd directives, as we did in SSMS in SQLCMD Mode. For example, the following set of statements includes several sqlcmd directives:

The connect command establishes a connection to a SQL Server instance, and the out command specifies that any query results should be outputted to the PersonData.txt file. Next come the T-SQL statements, followed by the GO command. After all this, we issue an exit command, which tells sqlcmd to quit interactive mode and return the command prompt window to its normal state.

Running sqlcmd in Command Mode

Interactive mode is useful when you want to run ad hoc queries at a command prompt. However, where you’re likely to gain the most benefit from the sqlcmd utility is by creating complete commands that you can run at a command prompt (in what I refer to as command mode), add to your Windows script files, run as part of SQL Server Agent jobs, or use in other ways.

The best way to learn how to create these types of commands is to return once again to the command prompt window. However, rather than switch to interactive mode, we can simply run our commands at the default prompt. For example, the following command retrieves data from the AdventureWorks2012 database on a local named instance of SQL Server:

The first issue worth pointing out is that the command, as shown here, might appear on multiple lines when you view it. In reality, this is a single-line command that is wrapping across multiple lines here because of margin limitations. However, you do not press Enter until you’ve entered the entire command.

The command itself starts with sqlcmd to launch the utility and then uses the -S switch to specify the SQL Server instance, as we sometimes do when entering interactive mode. Next, we use the -d switch to specify the database. (Note that switch names are case-sensitive.) Finally, we use the -Q switch to define our T-SQL statement, enclosed in double quotes. In this case, we use an uppercase Q to run the statement and immediately exit the sqlcmd environment. If we use a lowercase q, we’d end up in interactive mode. Figure 4 shows what the command prompt window looks like after I ran the command.

Figure 4: Running sqlcmd in command mode in the Windows console

Rather than returning the results to the command window, we can instead use the -o switch to output the data to a text file, as shown in the following example:

This time, we’re returning a list of employees from the Person table and ordering the data by name. When we run the command, the sqlcmd utility adds the employee names to the file, without returning results to the command prompt.

The sqlcmd utility also lets us run script files that contain the statements we want to execute. For example, suppose we create a script file that contains the following SELECT statement:

In this case, I’ve named the file EmployeeQuery.sql and saved it to the C:\DataFiles folder. To run the statement, I create a command that uses the -i switch to call the EmployeeQuery.sql file, as shown in the following example:

Once again, the results are outputted to the Employees.txt file.

At times, you might want to create a script file that you can use in multiple situations, in which case you might want to be able to pass in variable values when calling the file. To do so, you add placeholders to your script file that let you pass in the values.

For example, suppose you want to be able to specify the person type and last name when querying the Person table. You can create a script file that contains a T-SQL statement similar to the following:

Notice that, to specify the PersonType value in the WHERE clause, I use $(type). The type value serves as the variable name, which can be referenced when calling the file. To use it as a variable, I’ve enclosed it in parentheses, preceded those with a dollar sign, and enclosed the entire thing in single quotes. I then follow the same convention for the name variable.

I saved the file as EmployeeQuery2.sql. Now, when I call the file within my command, I also include the -v switch and specify values for the type and name variables, as shown in the following example:

Notice that I use a single -v argument and specify a value enclosed in double quotes for each variable. Once again, the command returns data about two employees whose last name is Smith. The query results are outputted to the Employees2.txt file.

Working with the sqlcmd Utility

Not surprisingly, the sqlcmd utility can do a lot more than what I’ve demonstrated here. As mentioned earlier, the utility supports a number of sqlcmd directives in addition to those we’ve covered. The utility also supports many more switches than what we’ve used in the examples. Again, refer to the topic “sqlcmd Utility” in SQL Server Books Online for more details about commands and switches. You’ll also find plenty of useful information in the Simple-Talk workbench “The SQLCMD workbench,” written by Robyn Page and Phil Factor. What you’re likely to find is that the more you dig into the sqlcmd utility, the better you’ll appreciate what a valuable tool it can be, especially if you want to automate and schedule tasks related to local and remote instances of SQL Server.