Getting Started with SQLCMD

Those of you who are regular readers may notice that my content has shifted towards scripting and some of the emerging platforms and tools for interacting with Microsoft SQL Server. There is a method to this madness but I can’t go into details yet on why. Just understand that this is building towards my efforts to preparing you for the brave new world that is SQL Server on Linux with touchpoints against your instances that are no longer just limited to Microsoft SQL Server Management Studio.

In this article I’m touching on the roots in the Microsoft SQL Server’s past and present to get you more familiar with the scripting syntax and methods you’ll be able to use in the future. I’m talking about SQLCMD.

SQLCMD is the command line tool used to interact with Microsoft SQL Server and is not a new tool but rather has been around for decades. You can not only use SQLCMD from the Command application integrated into windows but also from within SQL Server Management Studio (SSMS).

Many of the new tools and methods for interacting with Microsoft SQL Server builds on the syntax used by SQLCMD. What I hope to give you in this primer on SQLCMD is an understanding of:

Let’s take a look at these three goals of this article without further pause.

Launching SQLCMD from a Command Prompt

Before you can connect to a SQL Server instance using SQLCMD, you need to be able to launch SQLCMD. It goes without saying this is beyond simple but as my long-time readers know my approach is to take you from point-A to point-Z in a fashion where I don’t leave out steps or assume you know even the most simple of stages in a process. I find it frustrating if I’m reading about a topic and can’t accomplish what is being discussed because of missing information and I try to avoid that in my writing. In this case it is simple to launch SQLCMD. So simple I likely took up 5 times more space with explaining why I’m explaining something so rudimentary than I’ll take explaining the process:

Type Windows Key + R to display a run window, type cmd to launch the Command application, then at the prompt type sqlcmd followed by one of the many possible connection string combinations explained in the next session.

Upon a successful connection you’ll be met with a 1> prompt at which you can then start to interact with SQLCMD using the connection you’ve established. You can optionally also supply a query as part of the initial connection establishment for immediate processing as we’ll discuss further in the article as well.

Basic SQLCMD Syntax for Establishing a SQL Server Connection

Being a command line tool, SQLCMD is a combination of commands and parameters (aka options.) The following are the most widely used options as they’re required in part for establishing a connection to a SQL Server instance to execute statements against.

Server Option

-S:  Identifies the name of the server or serverinstance name in the case of a named instance.

Authentication Options

-U:  Identifies the user (login) name if using SQL Server Authentication

-P:  The password for the user specified in the –U option value supplied

-E: Denotes to use Trusted (Active Directory) Authentication. This is considered a default and can be omitted if using Trusted Authentication. The following are two examples of connection statements using SQLCMD:

First for Trusted Authentication against a default instance followed by a named instance

sqlcmd –S sqlcmd –S 

When using SQL Server Authentication the –S options remain identical, we simply supply a user name and password:

sqlcmd –S  -U  -P sqlcmd –S  -U  -P 

If using SQL Server Authentication you can omit the –P option for the sake of security and you’ll be prompted for the password upon execution.

Input Options

There are two ways to get your query submitted to the SQL Server instance: either an input file or an ad hoc query.

-Q or –q:  Query text to be submitted to the SQL Server instance

-i: Provides the full path and file name to be processed by the SQL Server instance.

Output Options

-o:  Provides the full path and file name to publish the results of the query.

There are many more options available than these. For more information you can always type

sqlcmd -?

When it comes to commands the two I want to highlight control kicking off script execution and exiting SQLCMD.

GO:  Unlike the t-sql counterpart you’re familiar with GO isn’t just a batch termination statement it actually is the command that will execute a command in SQLCMD.

EXIT:  This command will exit you from your current SQLCMD session. You’ll need to resupply the connection info once again to establish a live session afterwards.

Keep in mind that all of the options provided above are using syntax where you’re launching SQLCMD and connecting to a SQL Server instance all at once. If you’ve already launched SQLCMD and are met with a 1> prompt then you can eliminate the sqlcmd statement at the start of any of the code examples provided above.

Basic SQLCMD Syntax for Executing Queries

Armed with just those few options you’re able to do just about anything with SQLCMD. Let’s look at three different variations of using the previously explained options.

Scenario 1: Connect to default instance of SQL Server using SQL Authentication and execute an ad hoc command that lists the names of all databases to an output file

sqlcmd –S  -U  -P  -i "SELECT name FROM master;" –o c:tempdb.txt

Scenario 2: Connect to named instance of SQL Server using Trusted Authentication and execute a series of commands supplied from an input file displaying the results of the commands to an output file

sqlcmd –S   -i C:tempsqlscript.sql –o c:tempdb.txt

Scenario 3: Connect to default instance of SQL Server using Trusted Authentication and execute an ad hoc command that lists the names of all databases directly to the screen

sqlcmd –S  -i "SELECT name FROM master;"

Using SQLCMD from Within SQL Server Management Studio

Now that we’ve explored the basics of connecting to SQLCMD and executing queries from the command line I want to bring up the fact that you can also execute SQLCMD formatted scripts from within SQL Server Management Studio. This is a great method for continuing to use some of the advanced scripting functionality I’ll dive into with my next article on the subject of SQLCMD when you want to run a single script touching on multiple instances consecutively. (But more on than soon.)

From within SQL Server Management Studio open a new query window. Now you need to tell SSMS that this query will be running using SQLCMD. This is accomplished by going to the menu bar at the top of the screen and selecting Query then SQLCMD Mode.

Let’s build a simple connection to a server. We can do so using Trusted Authentication with a command as simple as:

:CONNECT 

The query immediately disconnects from the SQL instance at the end of the running of either the query text you’ve highlighted when executing the query or the full content of the query in the query window.

Something like the following demonstrates the ability to connect to a SQL Server instance (in this case I’ll use my local instance of SQL Server and returning the server name and listing of all databases on the instance:

:Connect .SELECT @@SERVERNAME, nameFROM master.sys.databasesORDER BY name;

Of course this is quite simplistic in terms of a query but the goal of this article is to provide an introduction into the options for scripting and using SQLCMD. In the next article I’ll get into using SQLCMD in SSMS to make life much easier when working with scripts that require multiple connections to accomplish a task.

Conclusion

Now that we have reached the end of this article you should be able to use SQLCMD from either the Windows Command application or from within SQL Server Management Studio. You should also be able to build a connection with just a few needed parameters that should look quite familiar if you’ve been working with SQL Server for a short period of time and pass a query into SQL Server via SQLCMD for processing. In the next article I am writing to introduce you to SQLCMD I’ll be explaining how SQLCMD can be used to allow a single script to run against multiple instances consecutively and why this is a powerful use of SQLCMD.