Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Introduction to SQL Server's sqlcmd utility


By:   |   Last Updated: 2017-06-30   |   Comments (1)   |   Related Tips: More > Database Administration

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 dont 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 didnt 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 its 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.

SQLCMD - Description: This is the most basic invokation of sqlcmd that allows you to connect to the default instance with your Windows login.

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.

Sqlcmd prompt - Description: Take a look on how the line number changes.

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 dont 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 users 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.

sqlcmd with SQL Server Authentication - Description: This is the way to connect to SQL Server using sqlcmd with 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


Last Updated: 2017-06-30


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, June 30, 2017 - 4:43:08 AM - Madison Quinn Back To Top

Hello,

 

Great stuff, You can run 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:

sqlcmd -S .\sqlsrv2012


Learn more about SQL Server tools