Consider the command line tool that is used to communicate with Microsoft SQL Server

Regular readers may have noticed that I have started to write more about scripts and some emerging platforms and tools for interacting with Microsoft SQL Server. There is an explanation for this, but I can’t go into detail yet. Suffice it to say that we are waiting for a wonderful new world of SQL Server on Linux, in which interaction with our instances is not limited to the environment of Microsoft SQL Server Management Studio.

This article will cover SQLCMD, a command-line tool that is used to interact with Microsoft SQL Server.

This is not a new tool, it appeared several decades ago. You can run SQLCMD not only from the Command Line application built into Windows but also from the SQL Server Management Studio (SSMS) environment.

Many of the 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 questions:

• Running SQLCMD from the command line.
• Basic syntax for connecting to Microsoft SQL Server using SQLCMD.
• Basic syntax for sending queries to a Microsoft SQL Server instance using SQLCMD.
• Execution of queries using SQLCMD from SQL Server Management Studio environment.

Starting SQLCMD from the command line

Before you can connect to Microsoft SQL Server using SQLCMD, you need to start it. Of course, this is very easy to do, but as you know, my approach is to walk you through the steps from A to Z without missing a single step or assuming that you know even the simplest steps of the process. It’s always a shame when you can’t follow 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 I probably needed five times more space to explain why I am going through such basic things in detail than to describe the process itself.

So, press the Windows + R key combination to open the runtime window, type cmd to start the Command application, and then type sqlcmd in response to 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 prompt like “1>” to start interacting with SQLCMD. If necessary, you can provide a request as part of the initial connection for immediate processing, as described later in this article.

Basic SQLCMD syntax for establishing a SQL Server connection

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

Server parameter

-S: Specifies the server name or server/instance name in the case of a named instance.

Authentication settings

-U: Specifies the user name when using SQL Server authentication;

-P: the password for the user specified in the -U parameter;

-E: denotes the use of trusted authentication (Active Directory). This option is selected by default and is not required when using trusted authentication. Below are two examples of SQLCMD connection instructions.

First, a trusted authentication is performed for the default instance followed by a named instance:

sqlcmd -S <server name>

sqlcmd -S <server name>\

<copy name>

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

sqlcmd -S <server name > -U

<username > -P <password>

sqlcmd -S <server name>\

<copy name>

-U <username> -P <password>

If you are using SQL Server authentication, you can omit the -P parameter for security reasons, and then you will be prompted for a password during execution.

Input parameters

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

-Q or -q: the text of the query sent to the SQL Server instance;

-i: provides the full path and name of the file being processed by the SQL Server instance.

Output parameters

-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, type

sqlcmd?

The two commands you should pay attention to control the script launch and exit from SQLCMD.

GO: unlike your familiar T-SQL counterpart, GO is not just a packet termination instruction, but a command that executes a command in SQLCMD.

EXIT: This command terminates the current SQLCMD session. You will then need to re-enter the connection information to establish an active session.

Remember that for all the above parameters the syntax is used to start SQLCMD and connect to the SQL Server instance at the same time. If you have already started SQLCMD and received the “1>” prompt, you can get rid of the sqlcmd instruction at the beginning of all the above program code fragments.

Basic SQLCMD syntax for executing queries

Using only these few parameters, almost any task can be solved with SQLCMD. Let’s consider three different applications of the parameters described above.

Scenario 1. Connect a default SQL Server instance using SQL authentication and execute the command that lists 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

Scenario 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>\

<copy name> -i

C:\temp\sqlscript.sql -o c:\temp\db.txt

Scenario 3. Connect a default SQL Server instance using trusted authentication and run a command that lists all database names directly on the screen:

sqlcmd -S <server name > -i

“SELECT name FROM master;”

Using SQLCMD from SQL Server Management Studio

Now that we have covered the main features of connecting to SQLCMD and executing queries from the command line, we will pay attention to the possibility of executing formatted SQLCMD scripts from SQL Server Management Studio environment. This is a great way to continue using the unique scripting features that I will cover in more detail in the next article on SQLCMD when you need to run a single script covering multiple instances in succession.

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

Let’s make a simple connection to the server. This can be done using trusted authentication and a simple command:

: CONNECT <server name>

The query is immediately disconnected from the SQL instance when the selected query text is completed when the query is executed or when the full content of the query is completed.

Something similar to the one below demonstrates the ability to connect to an instance of SQL Server (in this case a local instance of SQL Server 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 from a query perspective, but my job is to look at various options for scripting and using SQLCMD, and in the next article I will cover how to use SQLCMD in an SSMS environment, which makes it much easier to work with scripts that require multiple connections to perform the task.

So now you can use SQLCMD from the Windows Command application or SQL Server Management Studio environment. In addition, you can organize a connection with only a few necessary parameters familiar to everyone who has worked with SQL Server at least a little bit and send a query to SQL Server via SQLCMD for processing.