Friday, September 2, 2011

Connect SQL Server using Command Line - Using the sqlcmd Utility (SQL Server Express)

You can manage your Microsoft SQL Server 2008 Express (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.
Sqlcmd
  [-U login id] [-P password] [-S server] [-H hostname]
  [-E trusted connection] [-d use database name] [-l login timeout]
  [-N encrypt connection] [-C trust the server certificate]
  [-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] 

Connecting to SQL Server Express 

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

 
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 above arguments:
sqlcmd -S server1\SQLExpress

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.
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 
For More Information visit: MSDN Article on SQLCMD

No comments :

Post a Comment