SQL Server 2008 R2 CLI Tools
This explores various command-line utilities that ship with SQL Server. These utilities give administrators a different way to access the DB engine and its related modules. In some cases, they provide functionality that is also available with SQL Server's graphical UI (GUI). Other command-line utilities provide functionality that is available only from the command prompt. For each utility, this offers the command syntax along with the most commonly used options. For the full syntax and options available for the utility, see SQL Server Books Online.
Utility Install Location
sqlcmd -- C : : \Program Files\MS SQL ServerU 00\Tools\Binn
dta -- C : : \Program Files\MS SQL ServerU 00\Tools\Binn
tablediff -- C : : \Program Files\MS SQL Server\100\COM
bcp -- C : : \Program Files\MS SQL ServerU 00\Tools\Binn
sqldiag -- C : : \Program Files\MS SQL ServerU 00\Tools\Binn
sqlservr -- C : : \Program Files\MS SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
A few command-line utilities have been added in SQL Server 2008, however, and some have been removed. The sqlps utility is new to SQL Server 2008. This utility can be used to run PowerShell commands and scripts. The sqlps utility and the PowerShell Windows-based command-line management tool are discussed in detail in 17, "Administering SQL Server 2008 with PowerShell." Utilities removed from SQL Server 2008 comprise sac. The sac utility can be used in SQL Server 2005 to import or export settings available in the graphical Surface Area Config (SAC) tool. Both the sac command-line utility and SAC graphical tool have been removed. Similar functionality is now available via policy-based management and the Config Manager tool.
The sqlcmd Command-Line Utility
The sqlcmd command-line utility is the then generation of the isql and osql utilities that you can have used in prior versions of SQL Server. It offers the same type of functionality as isql and osql, including the ability to connect to SQL Server from the command prompt and execute T-SQL commands. The T-SQL commands can be stored in a script file, entered interactively, or specified as command-line arguments to sqlcmd.
--------------------------------------------------------------------------------------------
sqlcmd
[{ { -U login_id [ -P password ] } \ -E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -1 login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u Unicode output ] [ -r [ O | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format]]
[ -X [ 1 ] ] disable commands, startup script, environment variables [and exit]
[ -? show syntax summary ]
--------------------------------------------------------------------------------------------
The bcp Command-Line Utility
You use the bcp (bulk copy program) tool to address the bulk movement of data. This utility is bidirectional, allowing for the movement of data into and out of a SQL Server DB. bcp uses the subsequent syntax:
bcp { [ [DB_name. ] [owner]. ]{table_name \ view_name} \ "query"}
{i n | out | queryout | format} data_file
[-mmax_errors] [-fformat_file] [ -x] [-eerr_file]
[-Ffirst_row] [-Llast_row] [-bbatch_size]
[-n ] [-c ] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6]
[-q ] [-C { ACP | OEM | RA W | code_page } ] [-tfield_term]
[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
[-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
[-T] [-v ] [-R] [-k ] [-E] [-h"hint [,...n]" ]
Some of the commonly used options—other than the ones used to stipulate the DB, such as user ID, password, and so on—are the -F and -L options. These options allow you to stipulate the first and last row of data to be loaded from a file, which is especially helpful in large batches. The - t option allows you to stipulate the field terminator that separates data elements in an ASCII file. The -E option allows you to import data into SQL Server fields that are defined with identity properties.