Problem
You need to run SQL commands against a SQL Server instance using the Windows command line. You have heard about a utility named sqlcmd that allows you to execute SQL commands from the console, but you don’t know how to use it. In this tip I will give you insight about this tool and how to use it.
Solution
Usually the sqlcmd utility is not used much compared to SQL Server Management Studio (SSMS). I know a fellow colleagues that didn’t even know it exists. The reason behind this is that SSMS is a very robust tool that is the preferred choice for most day to day work, but there are cases where sqlcmd is a much better option.
That is especially the case if you are running SQL Server on Linux. Usually big companies have critical database servers in Demilitarized Zones (DMZs), so it’s unlikely that you may be able to connect to these servers via SSMS from your workstation. In other words, you may need to access the server with a Secure Shell (ssh) client and either run what you need to execute with sqlcmd or establish a tunnel and connect with SSMS.
What is sqlcmd?
Sqlcmd is a utility that is installed with the Client Tools when you install SQL Server. This tool allows you to execute Transact-SQL statements, stored procedures, and script files at the console. It also allows you to use predefined system variables and define your own user variables. As you may guess, the fact that sqlcmd allows execution of stored procedures makes it possible to run SQL Server Agent Jobs or Job Steps.
Another remarkable aspect of sqlcmd is that it allows connections to any version of SQL Server, something that you cannot do with SSMS. The reason behind this is that sqlcmd uses the ODBC driver instead of .NET System.Data.SqlClient Namespace which is part of ADO.NET which is used by SSMS.
Connecting sqlcmd to the Default SQL Server Instance
If you are connecting to a local server using Windows Authentication and your current user has access rights, then you can just invoke sqlcmd on the command prompt as follows and you will be connected to the default instance.
sqlcmd
If you have the proper permissions granted, you will see the sqlcmd prompt like on the following screen capture.
The 1> means the line number, which increases every time you press the ENTER key and restarts to 1 when you enter the GO batch separator as shown below.
On the above image you can see that the line number increases until the instruction GO is entered, then the sqlcmd prompt returns back to line 1 after executing the code.
Connecting sqlcmd to a SQL Server Instance Using Windows Authentication
If your organization uses Active Directory, you can use Trusted Authentication to connect to a remote or local server. You just need to specify the server and instance name with the -S parameter and -E to use Trusted Authentication. For those of you who don’t know what Trusted Authentication is, it is the way Microsoft refers to using the Windows login account to authenticate the user across different applications.
Here is how to connect to an instance. Substitute your server name and instance name as needed.
sqlcmd -S [SERVERNAME\INSTANCE] -E
Connecting sqlcmd to a SQL Server Instance Using SQL Server Authentication
If you need to connect using SQL Server authentication you need to also specify the -U parameter with the user name and the -P parameter with the user’s password, as follows. Again substitute the parameters with your values.
sqlcmd -S [SERVERNAME\INSTANCE] -U [username] -P [password]
On the next screen capture you can see that I have connected to a remote SQL Server instance running on Linux using SQL Server authentication.
Connecting sqlcmd to a SQL Server Instance with a Dedicated Administrator Connection (DAC)
There are circumstances where you cannot connect to SQL Server due to starvation of resources, as an example. SQL Server has a scheduler dedicated to provide access to just one connection in those cases. That is the Dedicated Administrator Connection (DAC). You cannot connect to the DAC as you do regularly with SQL Server Management Studio, because SSMS uses two connections, one for Object Explorer and another for the query window. In those cases sqlcmd is one of the best choices to connect you to the SQL Server instance and do what is needed to return the instance to a normal state.
In order to connect to a SQL Server instance using the DAC with sqlcmd you need to add the -A parameter.
If you connect with a Windows login then the invocation of sqlcmd is as follows:
sqlcmd -S [SERVERNAME\INSTANCE] -E -A
If you want to use a SQL Server login you have to invoke sqlcmd in the following way:
sqlcmd -S [SERVERNAME\INSTANCE] -U [username] -P [password] -A
Next Steps
- Another feature of sqlcmd is that it allows you to execute multiple files as a batch execution as explained in the following tip: Using SQLCMD to Execute Multiple SQL Server Scripts.
- In order to connect to a Dedicated Administrator Connection you first have to enable it. You can see how to do this on the following tip: Enable SQL Server Dedicated Administrator Connection.
- You can also use the batch separator (GO) to execute a batch a given number of times: Executing a TSQL batch multiple times using GO
- Another possibility with sqlcmd is the ability to find SQL Servers on our network: Find SQL Servers On Your Network with OSQL and SQLCMD.
