SQL Server SQLCMD Basics

Sqlcmd makes many SQL Server tasks, such as automating test runs and maintenance tasks, easier and quicker. The sqlcmd command-line utility is valuable to any database developer or DBA as the prime means of executing batches of SQL Statements to SQL servers, and saving results to file. Rob Sheldon gives you the basic facts about this great utility

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.

NOTE: The sqlcmd utility replaces the osql utility in earlier versions of SQL Server. Although the utility is still supported in SQL Server 2012, Microsoft plans to remove osql in a future version of SQL Server and recommends that you avoid using it for current development.

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.

NOTE: You can also click the SQLCMD Mode button on the toolbar if the button is there. If it’s not, you’ll have to manually add it by customizing your toolbar. The exact method for doing so varies from one SQL Server version to the next, so refer to SQL Server Books Online for details specific to your version.

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.

NOTE:  The GO command is a client-side ‘batch delimiter’ directive that is common to sqlcmd and SSMS. It is not a Transact SQL statement. it can be followed on the same line by the number of times you wish to run the preceding batch. Unless you specify this, it is just run once.

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.

Tags: , , , ,


  • Rate
    [Total: 10    Average: 4.7/5]
  • MatthewMonroe

    Tab delimited output
    Thanks for the informative tutorial. The text files created by SqlCmd are human readable, with space padding used to align the columns; it’s too bad they’re not tab-delimited. I found that you can use the -s switch to change the delimiter, and you can even use a tab by surrounding it with double quotes (though you have to run the command from a batch file, since the command prompt treats the tab as a tab-completion command). Example command:

    sqlcmd -S Server -d Database -i C:TempQuery.sql -o C:TempResults.txt -s" "

    Unfortunately, the extra spaces are still present in the output file. I tried BCP with -c, but it doesn’t include the column headers. Stackoverflow had some options, but they’re a bit ugly (http://stackoverflow.com/questions/1355876/). The only other option I could think of was to use sqlcmd and then use some sed commands to cleanup the text (using sed from GnuWin32).

    sed -r "s/( +t|t +)/t/g" C:TempResults.txt | sed -r "s/(^ +| +$)//g" | sed 2d | sed $d | sed "/^$/d" > C:TempResults_New.txt

    Is there an easier method to export the results of any query (not just a table) to a tab-delimited text file using the command line?

  • MatthewMonroe

    Sed update
    I found that by using the alternation operator in the first sed command, some of the results still had leading spaces. This can be fixed by replacing with two commands:

    [code]sed -r "s/ +t/t/g" C:TempResults.txt | sed -r "s/t +/t/g" | sed -r "s/(^ +| +$)//g" | sed 2d | sed $d | sed "/^$/d" > C:TempResults_New.txt[code]

  • Phil Factor

    Re: Tab Delimited Output
    I don’t know of any way that SQLCMD can be persuaded to output a proper tab-delimited file, which is an odd omission for a utility that has been updated over the years, but the article that Robert mentions that Robyn and I wrote a while back shows a work-around which is, I’ll admit, ugly.

  • miguel q

    sqlcmd to csv file.
    I use a simple script file to query backup age for my databases, then I put the sqlcmd on a for loop and run it agaisnt 100 servers, saving the output to a CSV file.
    This is what I do on the batch file in a nutshell:
    Rem crate my out file
    Echo server_name, database, age > fileout.CSV
    Rem do the loop
    For %%a in (serverlist.txt) do
    (Sqlcmd -s%%a -iscript.SQL -s"," >> file out.CSV)

    The out out is saved, and the emailed, and I can open using ms excell.

    Great article!
    PS: sorry for typos… Written from my tablet

  • Anonymous

    just an idea
    its was shared in mail is my idea
    pls implement if u can

  • Anonymous

    prevent file from bring truncated
    Is there a way to append data to an existing file with sqlcmd? I know you said the file is truncated by default, but I was wondering if there was a work around. I write data to a huge file daily and most of the records do not change. Thank you.

  • Phil Factor

    Re: Just an idea
    If you want to generate a report from SQLCMD, the easiest way is probably to use Database Mail http://technet.microsoft.com/en-us/library/ms175887%28v=sql.105%29.aspx which has succeeded good old, but deprecated, xp_sendmail

  • Phil Factor

    Re: prevent file from bring truncated
    The simplest way is probably to use the ‘double chevron’ in the command line >> with the piped output, as described here…
    ..but it means steering clear of using the -o command-line option or the :out directive. You can also put in an TYPE command to append the fily you’ve just created to the end of your result file, again using the ‘>>’ redirector

  • electrum

    execution order
    i noticed that in your article was this statement "your commands and T-SQL statements are executed in the specified order."

    however, i’ve had problems where the sqlcmd commands are executed first and then the TSql commands are executed.

    this is an example:
    :!! dir c:temp
    print ‘line 1’
    :!! dir c:temp
    print ‘line 2’

    the 2 ‘dir’ commands are executed first, and then the 2 ‘print’ lines.

    i discovered this in a script we had that deleted an SSIS pkg, then ran a sql query that checked that it was deleted, then re-added a new SSIS pkg, then ran a sql query that checked that it was added. the 2 sqlcmd commands that deleted then added the SSIS pkg executed first, then the sql query that was supposed to check that the first sqlcmd deleted the SSIS pkg executed and reported the pkg wasn’t deleted, because it had already been re-added.

    is there an explanation for this out-of-sequence execution?

  • MatthewMonroe

    Execution Order
    I replicated the behavior that Electrum sees, and I don’t have an explanation, but I do have a fix: add a GO command:

    :!! dir c:temp
    print ‘line 1’
    :!! dir c:temp
    print ‘line 2’

  • JWRebel

    TAB on commandline
    If you start a cmd sesssion with cmd /F:on there will be no file completion on hitting tab (can still evoke completion with Ctrl+F for files and Ctrl+D for directories)
    I use a tab.bat file to set the following as environment variables, subsquently using %tab% or %qtab%. (%qtab% is the same as "%tab%"). Note that TAB stands for the tab character. You can easily create the bat file with notepad.

    set qtab="TAB"
    set tab=TAB

  • JWRebel

    TAB-delimited output
    sqlcmd -S sqlServersdb0001 -d Data -i get.sql -W -s%qtab% -h-1 >>fileName.out

    * This will get you tab-delimited output without padding or headers
    * The result will create or append to the file indicated (use > to overwrite)
    * See above post for %qtab%; if inside a cmd /F:off window you could instead type -s"TAB"
    * Trailing or leading spaces in the actual values will remain
    * I recommend a character such as Ctrl+R or Ctrl+D as FS (field separator) — they generally do not occur the data; if you use the GnuWin32 utilites, sorting is better and cols don’t get confused with (aggregated) white space. You can always use <in tr

  • SteveWild

    Special access required
    Great article. One question, does the SQL login you use as part of the sqlcmd need to have specific permission to the database for example bulkadmin. I’d assumed that would only be the case if your command included bulk insert and any alter table or inserts would just need the relevant access on the table.

  • Phil Factor

    Re: Special access required
    As far as I’m aware, the requirements for SQLCMD are no different to the same batches in an SSMS query window, so you’d be right in your assumption. What makes you suspect that this isn’t so?

  • SteveWild

    Re: Special access required
    Thanks for the very quick reply. That’s good to know that my assumption is correct. My suspicions came after recently picking up a process that uses SQLCMD to deploy huge amounts of data and database structure changes. The sql login it uses has a number of special roles including BulkAdmin, so was concerned if that got taken away (due to new policies) it would break the process.

  • Az_

    SQLCMD for an instance with non-default port
    Thanks for this article. Had a question on this though. I have script for replication that connects to distributor, publisher, and subscriber, and executes appropriate scripts at the appropriate instances. Everything used to work fine if the instances are configured at their default ports, and SQL browser service is on. But now, to harden our SQL environment, we had reconfigured SQL ports to non-default ports, and have turned off the SQL browser discovery service. Also, went ahead and created an alias on the server/client/wherever this connection is cosumed to redirect to that non-default port – but the :CONNECT no longer works with non-default ports.

    :SETVAR PublisherServerInstance ABCD,12345

    :CONNECT $(PublisherServerInstance)

    SELECT @@ServerName

    Please advise. Any pointers would be greatly appreciated!


  • Az_

    SQLCMD for an instance with non-default port – fixed
    @DBArgenis responded with a pointer to the working non-default port, and when parameterized, it needs to be quoted.

    :SETVAR PublisherServerInstance "ABCD,12345"
    :CONNECT $(PublisherServerInstance)
    SELECT @@ServerName

  • sdoran

    How to execute command against a list of servers in a text file
    How can I change this to use a text file that is a list of server names to execute the sql command against.

  • Saptarshi ROY CHOUDHURY

    Is there anyway to get SQLCMD mode to run in a sql job step?