As a SQL Server Database Administrator or Developer, you may often need to execute Transact-SQL statements, stored procedures, or other SQL commands from a command prompt. Instead of launching SQL Server Management Studio (SSMS) or any other GUI, you can use the SQLCMD utility to quickly run queries against a SQL Server database instance.

The SQLCMD utility is a lightweight tool that allows you to connect to a production SQL Server instance and check server performance. It is available by default under the “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\” location.

To connect to a SQL Server Database Engine using the SQLCMD utility, you have two authentication options: Windows Authentication and SQL Server Authentication.

Connecting using Windows Authentication

To connect using Windows Authentication, follow these steps:

1. Open a Command Prompt window and navigate to the location where the SQLCMD utility is available on your machine (by default, “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\”).
2. Use the following command to connect to the default instance of the Database Engine on port 1433:

SQLCMD -S SERVERNAME -E

Alternatively, you can connect to a named instance using the following command:

SQLCMD -S SERVERNAME\INSTANCENAME -E

If the named instance is listening on a different port (e.g., port 1919), use the following command:

SQLCMD -S SERVERNAME\INSTANCENAME,1919 -E

Once connected, you can run queries against the database engine. For example, you can execute the following query to identify any blocking:

SELECT session_id, blocking_session_id 
FROM sys.dm_exec_requests 
WHERE session_id > 50

To close the SQLCMD session, type “EXIT” and press “Enter”.

Connecting using SQL Server Authentication

To connect using SQL Server Authentication, follow these steps:

1. Open a Command Prompt window and navigate to the location where the SQLCMD utility is available on your machine (by default, “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\”).
2. Use the following command to connect to the default instance of the Database Engine on port 1433:

SQLCMD -S SERVERNAME -U sa -P St0rangPa55w0rd

Alternatively, you can connect to a named instance using the following command:

SQLCMD -S SERVERNAME\INSTANCENAME -U sa -P St0rangPa55w0rd

If the named instance is listening on a different port (e.g., port 1919), use the following command:

SQLCMD -S SERVERNAME\INSTANCENAME,1919 -U sa -P St0rangPa55w0rd

Once connected, you can run queries as needed.

Using the SQLCMD utility provides a convenient way to interact with a SQL Server Database Engine without relying on a graphical interface. It is particularly useful for running simple or ad-hoc queries and checking server performance.

Remember to substitute “SERVERNAME” with the actual name of your SQL Server instance and “INSTANCENAME” with the name of the instance you want to connect to. Also, make sure to replace “St0rangPa55w0rd” with the appropriate password for the “sa” user.