Utilities – bcp, sqlcmd, sqlservr

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
• sqlservr

Following the description of these utilities, the DBCC command is described.

bcp Utility

bcp (Bulk Copy Program) is a useful utility that copies database data to/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} …]Code language: SQL (Structured Query Language) (sql)

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.

Data can be copied as either SQL Server–specific text or ASCII text. Copying data as SQL Server–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.3 shows the use of the bcp utility. (You have to execute this statement from a command line of your Windows operating system.)

Example 15.3

bcp AdventureWorks.Person.Address out "address.txt" –T –cCode language: SQL (Structured Query Language) (sql)

The bcp command in Example 15.3 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. BULK INSERT is described in Chapter “Modification of a Table’s Contents”.

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.

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]} …Code language: SQL (Structured Query Language) (sql)

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.4 shows the use of sqlcmd.

Example 15.4

sqlcmd –S NTB11901 –i C:\ms0510.sql –o C:\ms0510.rptCode language: SQL (Structured Query Language) (sql)

Table 15-2 – Most Important Options of the sqlcmd Utility

In Example 15.4, 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 administration 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.

Table 15-3 Most Important Commands of the sqlcmd Utility

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.5 shows the use of the exit command of the sqlcmd utility.

Example 15.5

1>USE sample;
2>SELECT * FROM project
3>:EXIT(SELECT @@rowcount)Code language: SQL (Structured Query Language) (sql)

This example displays all rows from the project table and the number 3, if the project table contains three rows.

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.

The sqlservr utility is invoked using the following command:

sqlservr option_listCode language: SQL (Structured Query Language) (sql)

option_list contains all options that can be invoked using the application. Table 15-4 describes the most important options.