The graphical administration tools provide just about everything you need to work with SQL Server. Still, there are times when you may want to work from the command line, especially if you want to automate installation, administration, or maintenance with scripts. The primary command-line tool is SQLCMD.EXE, which replaces OSQL.EXE and ISQL.EXE. Another command-line tool you’ ll use is BCP.EXE.
SQLCMD is a SQL query tool that you can run from the command line. Unlike OSQL and ISQL, which SQLCMD replaces, SQLCMD communicates with SQL Server only through the OLE DB API. Like OSQL and ISQL, SQLCMD has very little overhead, making it a good choice when system resources are a concern. Example 1-1 shows the syntax for SQLCMD.
Example 1-1. SQLCMD Syntax
sqlcmd [-U login id] [-P password] [-S servername[instancename]] [-H hostname] [-E trusted connection] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w screen width] [-a packetsize] [-e echo input] [-I Enable Quoted Identifier] [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [-W remove trailing spaces] [-u unicode output] [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile] [-z new password] [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit] [-k [1|2] remove[replace] control characters] [-y variable length type display width] [-Y fixed length type display width] [-p [1] print statistics[colon format]] [-R use client regional setting] [-b On error batch abort] [-v var = "value"...] [-A dedicated admin connection] [-X [1] disable commands[and exit with warning]] [-x disable variable substitution] [? show syntax summary]
Note
Unlike ISQL, SQLCMD does support connecting to named instances of SQL Server 2005. By default, SQLCMD connects to the default instance of SQL Server. If you specify the instance name as well as the server name, SQLCMD will connect to the specified instance name on the designated server.
When you start SQLCMD, you can issue Transact-SQL statements to run queries, execute stored procedures, and perform additional tasks. Because you’ re working at the command line, these commands are not executed automatically, and you need to use additional commands to tell SQLCMD when to execute statements, when to ignore statements, and so on. These additional statements must be entered on separate lines and are summarized in Table 1-2.
Table 1-2. SQLCMD Commands
Command | Description |
|---|---|
GO [count] | Executes all statements entered up to the previous GO or RESET. If count is used, the cached statements are executed the number of times specified in count as a single batch. |
RESET | Clears statements you’ ve entered so they aren’ t executed. |
ED | Calls the text editor, which is defined by the SQLCMDEDITOR environment variable, such as SET SQLCMDEDITOR=notepad. |
!! command | Executes the specified system command or script. |
QUIT | Exits SQLCMD. |
EXIT statement | Sets the exit statement. The batch or query is executed, and then SQLCMD quits. |
CTRL+C | Ends a query without exiting from SQLCMD. |
r filename | Sets the name of a file containing Transact-SQL statements to execute, which can include the GO command. |
:ServerList | Lists the locally configured servers and any network servers. |
:List | Prints the contents of the statement cache. |
:ListVar | Lists currently set variables. |
:Setvar | Sets variables. |
:Error filename | Redirects all error output to the specified file. |
:Out filename | Redirects all query results to the specified file. |
:Perftrace filename | Redirects all performance trace information to the specified file. |
:Connect | Connects to an instance of SQL Server or closes current connection. The syntax is :Connect [timeout] [ServerNameInstanceName] [Username] [Password] |
:Help | Displays SQLCMD help and command syntax. |
:On Error [exit|ignore] | Specifies how SQLCMD should handle errors when executing batch of SQL commands. SQLCMD can either quit executing the command or ignore the error and continue execution. |
In previous editions of SQL Server, you used ODBCPING to verify an ODBC connection between a client and server. In SQL Server 2005, OLE DB is the preferred technique for establishing database connections. You can establish a connection to a server for the purposes of testing and troubleshooting using SQLCMD –A. See Chapter 15 for an example of using SQLCMD–A.
BCP is the bulk copy program. You can use BCP to import and export data or copy data between instances of SQL Server 2005. The major advantage of BCP is its speed. It is much faster than standard database import/export procedures. Unfortunately, its command-line syntax makes it much harder to use.
The syntax for BCP is shown in Example 1-2.
Example 1-2. BCP Syntax
bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w Unicode characters]
[-N keep non-text native] [-V file format version] [-q quoted id]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]Table 1-3 provides a summary of key command prompt utilities included in SQL Server 2005. As the table shows, most command-line executables are stored in the %ProgramFiles%Microsoft SQL Server90ToolsBinn directory or in the directory for the SQL Server component to which they relate.
Table 1-3. Key Command-Line Tools for SQL Server 2005
Name | Description | Location |
|---|---|---|
bcp | Used to import and export data or to copy data between instances of SQL Server. | %ProgramFiles%Microsoft SQL Server90ToolsBinn |
dta | Used to analyze workloads and recommend optimization changes for that workload. | %ProgramFiles%Microsoft SQL Server90ToolsBinn |
dtexec | Used to configure and execute a SQL Server Integration Services (SSIS) package. The corresponding GUI tool is DTExecUI. | %ProgramFiles%Microsoft SQL Server90DTSBinn |
dtutil | Used to manage SQL Server Integration Services (SSIS) packages. | %ProgramFiles%Microsoft SQL Server90DTSBinn |
nscontrol | Used to create and manage instances of Notification Services. | %ProgramFiles%Microsoft SQL Server90NotificationServices9.0.242in |
profiler90 | Used to start SQL Server Profiler from a command prompt. | %ProgramFiles%Microsoft SQL Server90ToolsBinn |
rs | Used to run Reporting Services scripts. | %ProgramFiles%Microsoft SQL Server90ToolsBinn |
rsconfig | Used to configure a report server connection. | %ProgramFiles%Microsoft SQL Server90ToolsBinn |
rskeymgmt | Used to manage encryption keys on a Report Server. | %ProgramFiles%Microsoft SQL Server90ToolsBinn |
sac | Used to import or export surface area configuration settings between instances of SQL Server 2005. | %ProgramFiles%Microsoft SQL Server90Shared |
sqlagent90 | Used to start SQL Server Agent from a command prompt. | %ProgramFiles%Microsoft SQL Server<InstanceName>MSSQLBinn |
Default Instance: %ProgramFiles%Microsoft SQL ServerMSSQL.1MSSQLBinn | ||
sqlcmd | Used to perform administration and enter T-SQL statements at the command prompt. | %ProgramFiles%Microsoft SQL Server90ToolsBinn |
sqlmaint | Used to execute database maintenance plans created in previous versions of SQL Server. | %ProgramFiles%Microsoft SQL ServerMSSQL.1MSSQLBinn |
sqlservr | Used to start and stop an instance of the SQL Server Database Engine. | %ProgramFiles%Microsoft SQL ServerMSSQL.1MSSQLBinn |
tablediff | Used to compare the data in two tables and display differences. | %ProgramFiles%Microsoft SQL Server90COM |