Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Connecting to SQL Server Using SQLCMD Utility

MSSQLTips author Ashish Kumar Mehta By:   |   Read Comments   |   Related Tips: More > 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


Last Update: 8/25/2011


About the author
MSSQLTips author Ashish Kumar Mehta
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information