SQLCMD SQL Server Command Line Tool

JDBC for SQL Server - Herong's Tutorial Examples

∟SQLCMD SQL Server Command Line Tool

This section describes how to SQL Server command line tool - SQLCMD.

Before you start using Java programs to interact with SQL Server databases, you should learn some SQL Server client tools to run SQL statements on the SQL Server. SQLCMD, Query Analyzer, and Management Studio are all good client tools to use.

But SQLCMD is the simplest tool and it comes with the SQL Server Express Edition installation.

This tutorial shows you how to use SQLCMD, a command line client tool, to run SQL statements on your local SQL Server databases.

When "sqlcmd" is started and connected to a SQL Server, it will allow you to enter statements or commands. You can enter one or more statements in one or more lines to form a Transact-SQL statement batch.

By default, SQLCMD will be install automatically as part of the SQL Server installation. Its path name will be added to the %path% variable, so that you just enter "sqlcmd" to invoke it.

Here is command session that invokes sqlcmd, connects to the SQL Server, and runs a statement batch.

herong> sqlcmd -S localhost\SQLEXPRESS -E

1> SELECT DB_NAME();
2> GO

------------------------
master

(1 rows affected)

1>QUIT

Note that:

"-S localhost\SQLEXPRESS" option specifies where the SQL Server is located: on the "localhost" and named as "SQLEXPRESS".
"-E" option asks for a "trusted connection" using Windows Authentication of the current user.
The "GO" command sends one SQL statements as a batch to the server.
The returning output telling me that I was connected to the "master" database.

In newer versions of SQL Server, the Authentication Mode is set to Windows Authentication only. In this case, the admin user "sa" is disabled. In older versions, Authentication Mode is set to both SQL Server login and Windows Authentication. In that case, you are prompted to enter a password for "sa" and you can login with "sa" as shown below:

herong> sqlcmd -S localhost\SQLEXPRESS -U sa -P HerongY@ng

By the way, if you forget to specify the database instance name (\SQLEXPRESS) in the "-S" option, you will get the following error:

herong> sqlcmd -S localhost -E

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server :
   Named Pipes Provider: Could not open a connection to SQL Server [2]
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server :
   Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server :
   A network-related or instance-specific error has occurred while
   establishing a connection to SQL Server. Server is not found or not
   accessible. Check if instance name is correct and if SQL Server is
   configured to allow remote connections. ...

If you are running older versions, "sqlcmd" command files are located at different directories. Here are some examples:

\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\tools\binn
\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn
\Program Files\Microsoft SQL Server\100\Tools\Binn

To manage SQL Server, Click on "Start > All Programs > Microsoft SQL Server 2019 > SQL Server 2019 Configuration Manager". You will see 3 services listed under "SQL Server Services": SQL Server Browser (Stopped), SQL Server (Running), SQL Server Agent (Stopped).