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:

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).

To shutdown and start SQL server, select "SQL server" service and click "Action > Stop|Start" menu.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

Microsoft SQL Server Express Edition

 What Is Microsoft SQL Server Express Edition

 Installing Microsoft SQL Server Express Edition

 Installing Microsoft SQL Server 2014 Express Edition

SQLCMD SQL Server Command Line Tool

 Installing AdventureWorks Sample Database

 Create Login User in SQL Server

 Microsoft JDBC Driver for SQL Server

 Microsoft JDBC Driver - Query Statements and Result Sets

 Microsoft JDBC Driver - DatabaseMetaData Object

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Archived Tutorials

 References

 Full Version in PDF/EPUB