Utilities are components that provide different features such as data reliability, data definition, and statistics maintenance functions. The following utilities are described next:
- bcp
- sqlcmd
- mssql-cli
- sqlservr
NOTE The first three utilities—bcp, sqlcmd, and mssql-cli—are cross-platform utilities, meaning that you can use them with Windows as well as with macOS and Linux operating systems. The Windows installation of bcp and sqlcmd is performed when you install the corresponding instance of the Database Engine. The Linux installation of bcp and sqlcmd is similar to the installation of mssql-cli, which is described in detail later in this chapter using the Ubuntu operating system.
1. bcp Utility
bcp (Bulk Copy Program) is a useful utility that copies database data to or from a data file. Therefore, bcp is often used to transfer a large amount of data into a Database Engine database from another relational DBMS using a text file, or vice versa.
The syntax of the bcp utility is
bcp [[db_name.]schema_name.]table_name {IN | OUT | QUERYOUT | FORMAT}
file_name [{-option parameter} …]
db_name is the name of the database to which the table (table_name) belongs. IN or OUT specifies the direction of data transfer. The IN option copies data from the file_name file into the table_name table, and the OUT option copies rows from the table_name table into the file_name file. The FORMAT option creates a format file based on the options specified. If this option is used, the option -f must also be used.
NOTE The IN option appends the content of the file to the content of the database table, whereas the OUT option overwrites the content of the file.
Data can be copied as either specific text or standardized (ASCII) text. Copying data as specific text is referred to as working in native mode, whereas copying data as ASCII text is referred to as working in character mode. The parameter -n specifies native mode, and the parameter -c specifies character mode. Native mode is used to export and import data from one system managed by the Database Engine to another system managed by the Database Engine, and character mode is commonly used to transfer data between a Database Engine instance and other database systems.
Example 15.7 shows the use of the bcp utility. (You have to execute this statement from a command line of your Windows operating system.)
Example 15.7
bcp AdventureWorks.Person.Address out “address.txt” -T -c
The bcp command in Example 15.7 exports the data from the address table of the AdventureWorks database in the output file address.txt. The option -T specifies that the trusted connection is used. (Trusted connection means that the system uses integrated security instead of the SQL Server authentication.) The option -c specifies character mode; thus, the data is stored in the ASCII file.
NOTE Be aware that the BULK INSERT statement is an alternative to bcp. It supports all of the bcp options (although the syntax is a bit different) and offers much greater performance.
To import data from a file to a database table, you must have INSERT and SELECT permissions on the table. To export data from a table to a file, you must have SELECT permission on the table.
2. sqlcmd Utility
sqlcmd allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt. The general form of this utility is
sqlcmd {option [parameter]} …
where option is the specific option of the utility, and parameter specifies the value of the defined option. The sqlcmd utility has many options, the most important of which are described in Table 15-2.
Example 15.8 shows the use of sqlcmd. (You have to execute this statement from a command line of your Windows operating system.)
NOTE Before you execute Example 15.8, you have to change the server name and make sure the input file is available.
Example 15.8
sqlcmd -S NTB11901 -i C:\ms0510.sql -o C:\ms0510.rpt
In Example 15.8, a user of the database system named NTB11900 executes the batch stored in the file ms0510.sql and stores the result in the output file ms0510.rpt. Depending on the authentication mode, the system prompts for the username and password (SQL Server authentication) or just executes the statement (Windows authentication).
One of the most important options of the sqlcmd utility is the -A option. As you already know from Table 15-2, this option allows you to start a dedicated administrator connection (DAC) to an instance of the Database Engine. Usually, you make the connection to an instance of the Database Engine with SQL Server Management Studio. But, there are certain extraordinary situations in which users cannot connect to the instance. In that case, the use of the DAC can help.
The DAC is a special connection that can be used by DBAs in case of extreme server resource depletion. Even when there are not enough resources for other users to connect, the Database Engine will attempt to free resources for the DAC. That way, administrators can troubleshoot problems on an instance without having to take down that instance.
The sqlcmd utility supports several specific commands that can be used within the utility, in addition to Transact-SQL statements. Table 15-3 describes the most important commands of the sqlcmd utility. Example 15.9 shows how a query in relation to the sample database can be executed using this utility. (To execute Example 15.9, start sqlcmd, and at the command prompt type each line separately.)
Example 15.9
1>USE sample;
2>SELECT * FROM project
3>:EXIT(SELECT @@rowcount)
This example displays the number of rows of the project table (e.g., the number 3 if project contains three rows).
3. mssql-cli Utility
mssql-cli is an open source, cross-platform, interactive command-line query tool for the Database Engine. This tool can be used as an alternative to the sqlcmd utility.
This section demonstrates the Windows installation and the Ubuntu installation of the tool, as well as how you can use it. The final subsection compares the properties of mysql-cli to those of the sqlcmd utility.
3.1. Windows Installation of mssql-cli
The mssql-cli utility is implemented in Python. Python is not installed by default on Windows. Therefore, before you install mssql-cli, you have to install Python.
You can download the latest Python installation package from https://www.python.org/ downloads/. When installing, check the Add Python to PATH option, which you can find as the last line in the installation screen. That way, the necessary information concerning Python directories and files can be found by the system in the PATH environment variable.
Once Python is installed and the PATH environment variable is set, open a command prompt and install mssql-cli using the following command:
pip install mssql-cli
Figure 15-2 shows the installation process of the mssql-cli utility under Windows.
3.2. Ubuntu Installation of mssql-cli
As you already know, mssql-cli is a tool based on the Python programming language. You can find the source code for this tool on GitHub. To install the tool, go to https://github.com/dbcli/ mssql-cli. Click Installation Guide under the Ubuntu platform. The page that appears contains different forms of installations. Scroll down to “Installation for latest preview version via Direct Downloads.” Download the Debian package and save the .deb file under the desired location.
Go to the download folder and double-click the file to launch it. It opens the screen shown in Figure 15-3. Click Install. After that, you will be prompted to enter the credentials for the authentication.
NOTE As you can see from the description, the mssql-cli installation process is similar to the installation process of Azure Data Studio (see Chapter 3). Analogously, you do not get any message when the installation of mssql-cli is finished.
4. Using mssql-cli
The mssql-cli utility allows you to write T-SQL statements, system procedures, and script files at the command prompt. The general form of this utility is
mssql-cli {option [parameter]} …
where option is the specific option of the utility, and parameter specifies the value of the particular option. The utility has many options, the most important of which are described in Table 15-4.
NOTE The functionality of mssql-cli is identical for all platforms.
Example 15.10 shows the use of the mssql-cli tool. (You have to execute this statement from a command line of your Windows operating system.)
NOTE Before you execute Example 15.10, you have to change the server name.
Example 15.10
mssql-cli -S LAPTOP-TVUM0CNL -U sa
In Example 15.10, a connection to the Database Engine instance LAPTOP-TVUM0CNL will be established for the sa user. (You will be prompted by the system to type the corresponding password.)
Figure 15-4 shows how you can establish a connection (with Windows Authentication) and execute T-SQL statements.
As you can see from Figure 15-4, the connection to the server instance is established using a trusted (integrated) connection. The system establishes the connection and shows you the name of the default database (“master>”). The USE statement changes the default database to sample. After that, you can write any T-SQL statement in relation to that database.
5. mssql-cli vs. sqlcmd
The following list describes the most important properties of mssql-cli and compares them to the corresponding properties of sqlcmd:
- mssql-cli is an open source contribution from Microsoft, with its source code available at GitHub, whereas sqlcmd is Microsoft proprietary software.
- mssql-cli has user-friendly interactive features like T-SQL IntelliSense, multiline editing, syntax highlighting, and formatting of results. (You can see most of these features by looking at the output of the SELECT statement in Figure 15-4.) sqlcmd is a simple command-line tool with none of these interactive features.
- mssql-cli and sqlcmd are both cross-platform utilities, meaning that you can use them with Windows as well as with macOS and Linux derivatives. mssql-cli is a new tool and does not have all the command arguments available in sqlcmd. For example, at the time of writing, the tool does not have an option to specify the output file.
- Generally, the mssql-cli tool can be used as an enhanced alternative to sqlcmd. On the other hand, sqlcmd is a mature tool and has more options.
6. sqlservr Utility
The most convenient way to start an instance of the Database Engine is automatically with the boot process of the computer. However, certain circumstances might require different handling of the system. Therefore, the Database Engine offers, among others, the sqlservr utility for starting an instance.
NOTE You can also use Management Studio or the net command to start or stop an instance of the Database Engine.
The sqlservr utility is invoked using the following command:
sqlservr option_list
option_list contains all options that can be invoked using the application. Table 15-5 describes the most important options.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.