By: Ashish Kumar Mehta | Comments | Related: > Tools
Problem
One of the junior SQL Server Database Administrators in my company asked me how he can connect to SQL Server Database Engine using the SQLCMD utility. In this tip I show how to connect to a SQL Server Database Engine using the SQLCMD utility with Windows Authentication and SQL Server Authentication.
Solution
Using the SQLCMD utility you can execute Transact-SQL statements, stored procedures or any other SQL commands from a command prompt instead of having to launch SSMS or some other GUI. This is a very effective utility available to SQL Server Database Administrators or Developers to run Simple or Ad-Hoc queries against a SQL Server database instance.
SQLCMD is a light weight utility which can be used to connect to Production SQL Server Instance to quickly check the server performance. I would also recommend you read "Enabling Dedicated Administrator Connection Feature in SQL Server 2008" which discusses using the Dedicated Administrator Connection feature. This feature can be used by SQL Server Database Administrators to connect to a SQL Server Instance when the database engine is not responding to regular connections.
When you are using the SQLCMD utility from a command line, SQLCMD uses the OLEDB provider to connect to SQL Server. However, SQL Server Management Studio (SSMS) uses the Microsoft .Net SqlClient Data Provider. You can query the DMV sys.dm_exec_sessions to see the provider used by looking at the client_interface_name column.
The SQLCMD utility is available by default under "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\" location.
To get a list of the different command options you can run SQLCMD -? at a command prompt as shown in the below snippet.
In this tip, we will take a look at how to connect to a database engine using the SQLCMD utility using Windows Authentication and SQL Server Authentication and then run a query to identify if there is any blocking.
Using SQLCMD Connect to a SQL Server Database Engine Using Windows Authentication
Open a Command Prompt window and browse to the location where the SQLCMD utility is available on your machine. By default you will be able to see the SQLCMD utility under "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\" location.
The below examples show how to connect to the default instance of the Database Engine on port 1433, a named instance and a named instance listening on port 1919. (You will need to substitute your server name and instance name to connect)
--Default Instance SQLCMD -S SERVERNAME -E --OR --Named Instance SQLCMD -S SERVERNAME\INSTANCENAME -E --OR SQLCMD -S SERVERNAME\INSTANCENAME,1919 -E
Once you are connected to the database engine using SQLCMD utility, copy and paste the sample blocking code query below in the SQLCMD window and press "Enter". Then type "GO" and press "Enter" to execute the query.
SELECT session_id, blocking_session_id FROM sys.dm_exec_requests WHERE session_id > 50
Here you can see what the output looks like:
To close a SQLCMD session type "EXIT" and press "Enter" as shown in the above snippet.
Using SQLCMD Connect to a SQL Server Database Engine Using SQL Server Authentication
Open a Command Prompt window and browse to the location where the SQLCMD utility is available on your machine. By default you will be able to see the SQLCMD utility under "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\" location.
The below examples show how to connect to the default instance of the Database Engine on port 1433, a named instance and a named instance listening on port 1919. (You will need to substitute your server name and instance name to connect). For this connection we need to also specify the -User and -Password parameters as well.
--Default Instance SQLCMD -S SERVERNAME -U sa -P St0rangPa55w0rd --OR --Named Instance SQLCMD -S SERVERNAME\INSTANCENAME -U sa -P St0rangPa55w0rd --OR SQLCMD -S SERVERNAME\INSTANCENAME,1919 -U sa -P St0rangPa55w0rd
Once you are connected to the database engine using SQLCMD utility you can run the above blocking query or any other query to test.
Next Steps
- Read the following tip Enabling Dedicated Administrator Connection Feature in SQL Server as this feature will be helpful for a Database Administrator to connect to a SQL Server Instance when the database engine is not responding to regular connections.
- Read these related tips:
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
