SQLCMD – Learning to work with SQLCMD

SQLCMD – Let’s consider a command line tool that is used to interact with Microsoft SQL Server. In this article we will talk about SQLCMD, a command line tool that is used to interact with Microsoft SQL Server.

This is not a new tool, it appeared a few decades ago. You can run SQLCMD not only from a command line application built into Windows, but also from SQL Server Management Studio (SSMS).

Many new tools and methods for interacting with Microsoft SQL Server are based on the syntax used by SQLCMD. I have taken up this introductory article to help you understand the following:

1. Run SQLCMD from the command line.
2. Basic syntax for connecting to Microsoft SQL Server using SQLCMD.
3. The basic syntax for passing requests to an instance of Microsoft SQL Server using SQLCMD.
4. Executing queries using SQLCMD from SQL Server Management Studio.

Running SQLCMD from the command line

Before you can connect to Microsoft SQL Server using SQLCMD, you must run it. Of course, it is very easy to do, but as you know, my approach is to walk you through the steps from A to Z without missing any action or assuming that you know even the simplest steps of the process. It’s always a shame when you can’t perform the steps described in the instructions because you don’t have some details. In my articles I try to avoid such omissions. In this case, running SQLCMD is so easy that it probably took me five times as much space to explain why I take such basic things apart from the process itself.

So, press the Windows + R key combination to open a runtime window, type cmd to start the Command application, and then type sqlcmd as the prompt, followed by one of the many possible combinations of connection strings that are described below.

After a successful connection, you will receive a “1>” prompt to start communicating with SQLCMD. If necessary, you can submit the prompt as part of the initial connection for immediate processing, as described later in this article.

SQLCMD basic syntax for setting up an SQL Server connection

As a command line tool, SQLCMD is a combination of commands and parameters. Below are the most common parameters required to establish a connection to the SQL Server instance where the instructions are executed.

Server parameter

-S: specifies the server name or the server/instance name in the case of a named instance.
Authentication Options
-U: specifies the user name when using SQL Server authentication;
-P: password for the user specified in -U;
-E: indicates that you are using trusted authentication (Active Directory). This option is selected by default and does not have to be entered when using trusted authentication. Below are two examples of instructions for connecting using SQLCMD.

First, you perform the trusted authentication for the default instance followed by the named instance:

sqlcmd -S <server name>
sqlcmd -S <server name>\
<instance name>

When using trusted authentication, the -S parameters remain identical, just enter your username and password:

sqlcmd -S <server name > -U
<username > -P <password>
sqlcmd -S <server name>\
<instance name>
-U <username> -P <password>

When using SQL Server authentication, you can omit the -P parameter for security reasons, and then a password will be requested at runtime.

Input Options

There are two ways to pass a query to an SQL Server instance: as an input file or as an unregulated query.

-Q or -q: the text of the query passed to the SQL Server instance;
-i: provides the full path and name of the file processed by the SQL Server instance.

Output Options

-o: provides the full path and file name for publishing the query results.

In fact, the list of parameters is much larger. For more information, enter the command:

sqlcmd -?

Two commands to pay attention to control the launch of the script and the exit from SQLCMD.

• GO: unlike the familiar to you analogue of T-SQL, GO – not just an instruction to complete the package, and the command that runs the command in SQLCMD.
• EXIT: this command completes the current SQLCMD session. Subsequently, to set an active session, you will need to re-enter the connection information.

Keep in mind that all of these parameters use the syntax that ensures that SQLCMD starts and connects to the SQL Server instance at the same time. If you have already started SQLCMD and received a “1>” prompt, you can get rid of the instruction sqlcmd at the beginning of all the above code fragments.

SQLCMD basic syntax for executing SQLCMD queries

Using only these few parameters, SQLCMD can solve almost any problem. Let’s consider three different applications of the parameters described above.

Example 1. Connect the SQL Server instance by default using SQL authentication and execute the command that makes a list of all database names in the output file:

sqlcmd -S <server name> -U
<username> -P <password>
-i "SELECT name FROM master;"
-o c:\temp\db.txt

Example 2. Connect a named instance of SQL Server using trusted authentication and execute a sequence of commands from the input file, displaying the results of the command in the output file:

sqlcmd -S <server name>\
<instance name> -i
C:\temp\sqlscript.sql -o c:\temp\db.txt

Example 3. Connect the default SQL Server instance using trusted authentication and run the command that displays a list of all database names directly on the screen:

sqlcmd -S <server name > -i
"SELECT name FROM master;"

Using SQLCMD from SQL Server Management Studio environment

Now that we have considered the main features of connecting to SQLCMD and executing queries from the command line, let us pay attention to the ability to execute formatted SQLCMD scripts from SQL Server Management Studio environment. This is a great way to continue using the unique scripting functions, which I’ll cover in more detail in the next article on SQLCMD when you need to run a single script covering several instances in sequence.

Open a new query window in SQL Server Management Studio. You need to let SSMS know that this query will be executed using SQLCMD, go to the menu bar at the top of the screen and select Query and then SQLCMD Mode.

Perform a simple connection to the server. You can do this using trusted authentication and a simple command:

: CONNECT <server name>

The query is immediately disconnected from the SQL instance upon completion of the query text you selected when executing the query or the full query content.

Something similar to the option below demonstrates the ability to connect to an SQL Server instance (in this case a local SQL Server instance is used) and return the server name and a list of all databases on the instance:

: Connect.
SELECT @@SERVERNAME, name
FROM master.sys.databases
ORDER BY name;

Of course, this is a simplified approach in terms of querying, but my job is to look at the different options for scripting and using SQLCMD. In the next article I will cover the use of SQLCMD in an SSMS environment, which makes it much easier to work with scripts that require multiple connections to perform a task.

So, you can now use SQLCMD from either the Windows Command application or from SQL Server Management Studio. In addition, you can organize a connection with just a few necessary parameters familiar to anyone who has worked with SQL Server at least a little, and pass the query to SQL Server through SQLCMD for processing.

SQLCMD alternative