Share via

Using the sqlcmd Utility (SQL Server Express)

  • Article

You can manage your Microsoft SQL Server 2005 Express Edition (SQL Server Express) databases by using the sqlcmd utility from the command prompt. To access the sqlcmd utility, click Start, click Run, and type sqlcmd.exe.

SQLCMD Syntax

The following arguments are available when you run the sqlcmd utility. For information about specific arguments, see sqlcmd Utility and sqlcmd Utility Tutorial in the SQL Server 2005 Books Online.

Sqlcmd
  [-U login id] [-P password] [-S server] [-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 Identifiers]
  [-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]
  [-f <codepage> | i:<codepage>[,o:<codepage>]]
  [-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"...]
  [-X[1] disable commands[and exit with warning]]
  [-? show syntax summary]

SQLCMD Commands

The following table provides an overview of commands that you can use from within the sqlcmd utility. For more information about these commands, see sqlcmd Utility in SQL Server 2005 Books Online.

Command Description

go [count]

Executes any cached Transact-SQL statements. When specifying a value for count, the cached statements will be executed count times as a single batch. For example, the following example will execute the two statements twice:

1> Use MyDatabase
2> SELECT FirstName, LastName FROM Employee WHERE EmployeeID < 10
3> GO 2

reset

Clears the statement cache.

ed

Lets you edit the current or last executed statement batch. To edit the last executed batch, this command must be typed immediately after the last batch has executed.

!! <command>

Executes operating system commands. To execute an operating system command, start a line with two exclamation marks (!!) followed by the operating system command. For example, the following performs a directory listing of the current drive:

1> !! dir

quit

- or -

exit

Exits sqlcmd immediately.

exit [(statement)]

Lets you use the result of a SELECT statement as the return value from sqlcmd.

The exit() statement with nothing between the parentheses executes everything preceding it in the batch, and then exits with no return value.

You can also include the exit parameter as part of a batch file. For example:

sqlcmd /Q "exit(SELECT COUNT(*) FROM '%1')"

:r <filename>

Parses Transact-SQL statements and sqlcmd commands from the file specified by filename into the statement cache. The file will be read and executed after a batch terminator (go) is issued.

:serverlist

Lists the locally-configured servers and the names of the servers broadcasting on the network.

:list

Prints the content of the statement cache.

:listvar

Displays a list of the scripting variables that are currently set.

:error <destination>[ filename | STDERR | STDOUT]

Redirects all error output to the file specified by filename, to the STDERR stream, or to the STDOUT stream. By default, error output is sent to STDERR.

:out <destination>[ filename | STDERR | STDOUT ]

Redirects all query results to the file specified by filename, to the STDERR stream, or to the STDOUT stream. By default, output is sent to STDOUT.

:perftrace <destination>[ filename | STDERR | STDOUT ]

Redirects all performance trace information to the file specified by filename, to the STDERR stream, or to the STDOUT stream. By default, performance trace output is sent to STDOUT.

:connect server_name[\instance_name] [-t timeout] [-U username [-P password] ]

Connects to an instance of SQL Server and closes the current connection.

:on error [ exit | ignore ]

Sets the action to be performed when an error occurs during script or batch execution.

When the exit option is used, sqlcmd exits with the appropriate error value.

When the ignore option is used, sqlcmd disregards the error and continues executing the batch or script. By default, an error message will be printed.

:help

Lists the sqlcmd commands and a short description of each command.

Connecting to SQL Server Express

When you connect to a SQL Server Express server, you must specify the server name and, if SQL Server Express is installed in a named instance, the instance name. By default, sqlcmd uses Windows Authentication. If you are connecting to the SQL Server Express server by using SQL Server Authentication, you must also provide the logon information for connecting to the SQL Server Express server. For example, if you are connecting to a default installation of SQL Server Express on a server named server1, you might use the following arguments:

sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword

Note

If you use the -P argument, your password appears in plain text at your command prompt. If you omit the -P argument, you will be prompted to enter your password. The text you enter will be hidden.

If you are logged on with a user account that is trusted on the server that is running SQL Server Express, you can omit the -U and -P arguments:

sqlcmd -S server1\SQLExpress

Note

The -E argument specifies a trusted connection. This is the default setting for sqlcmd, and so the -E argument can be omitted.

Example

The following example shows how to use the sqlcmd utility to connect to an instance of SQL Server Express, named SqlExpress, on a server named SqlServer1:

Sqlcmd -S SqlServer1\SqlExpress

After you are connected, you can issue other commands to manage the instance. For example, the following commands will display all currently installed databases. This is a convenient way to find the logical name of an attached SQL Server Express database:

SELECT name from sys.databases
Go

sp_configure system stored procedures

You can manage and optimize SQL Server Express resources through configuration options by using the sp_configure system stored procedure. For more information about sp_configure, see Setting Server Configuration Options in SQL Server 2005 Books Online.

The following configuration options are part of sp_configure, but are not supported in SQL Server Express:

  • Agent XPs
  • Database Mail XPs
  • SQL Mail XPs
  • Web Assistant Procedures

See Also

Other Resources

Working With SQL Server Express
SQL Server 2005 Books Online Download Center

Help and Information

Getting SQL Server 2005 Assistance