Connecting to SQL Server Using SQLCMD Utility

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.


How
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 1933.  (You will need to substitute your server name and instance name to connect).
--Connection SQL Server using Windows Authentication
--Default Instance
SQLCMD -S SERVERNAME –E
--Named Instance
SQLCMD -S SERVERNAME\INSTANCENAME –E
--OR
SQLCMD -S SERVERNAME\INSTANCENAME,1933 –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


To close a SQLCMD session type "EXIT" and press "Enter" as shown in the above snippet.


--Connecting SQL Server Using SQL Server Authentication
For this connection we need to also specify the -User and -Password parameters as well.


--Default Instance
SQLCMD -S SERVERNAME –U sa –P SQLPassword 
--Named Instance
SQLCMD -S SERVERNAME\INSTANCENAME –U sa –P SQLPassword 
--OR
SQLCMD -S SERVERNAME\INSTANCENAME,1933 –U sa –P SQLPassword 


Once you are connected to the database engine using SQLCMD utility you can run the above blocking query or any other query to test.