SQL Server 2005 Command Line Tool "SQLCMD" - Part I

January 17, 2007

Microsoft shipped the SQL Server 2005 command line tool “SQLCMD” along with SQL Server. This SQLCMD replaces the old ISQL command line utility and the OSQL utility. This series will illustrate the various ways of using the SQL Server 2005 command line utility “SQLCMD”.

When “SQLCMD” is run from the MS-DOS command prompt, it uses the OLE DB provider and when it executed from the SQL Server management studio, it uses .NET SQLClient for execution.

Method 1

SQLCMD can be used as an interactive command line SQL tool. Let us see all the options that are available in the SQLCMD tool. Execute the following command in MS-DOS prompt.

SQLCMD/?

The following results are returned. [Refer Fig 1.0]

usage: 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]        [-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, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Click for larger image

Fig 1.0

Method 2

SQLCMD can be used as an interactive command line SQL tool. The example below illustrates how to connect to SQL Server and query the server name and version number. Execute the following command at the MS-DOS command prompt as shown below. You will get the following results.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>SQLCMD -SHOME -E
1> select @@servername
2> Select @@version
3> go
--------------------------------------------------------------------------------
------------------------------------------------
HOME
(1 rows affected)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
        May  3 2005 23:18:38
        Copyright (c) 1988-2003 Microsoft Corporation
        Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 rows affected)
1> quit
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>

Note: HOME is the servername and –E means we are using windows authentication.

Method 3

The above command can be executed using SQL Authentication as shown below. This example shows how to connect to a named instance of the SQL Server using SQL authentication.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>
SQLCMD -SHOME\SQLEXPRESS -Usa -P******
1> select @@servername
2> Select @@version
3> go
--------------------------------------------------------------------------------
------------------------------------------------
HOME\SQLEXPRESS
(1 rows affected)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
        Apr 14 2006 01:12:25
        Copyright (c) 1988-2005 Microsoft Corporation
        Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 rows affected)
1> quit
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>

Note: HOME is the hostname, SQLEXPRESS is the instance name servername and –U and –P mean we are using SQL authentication.

Method 4

The following example illustrates how to connect to a named instance of the SQL Server using Hostname and PORT number.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>
SQLCMD -Stcp:HOME,7005 -Usa -P********
1> select @@servername
2> select @@version
3> go

--------------------------------------------------------------------------------
------------------------------------------------
HOME\SQLEXPRESS

(1 rows affected)


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
        Apr 14 2006 01:12:25
        Copyright (c) 1988-2005 Microsoft Corporation
        Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Se
rvice Pack 2)

(1 rows affected)
1> quit
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>
Note: Home is the hostname, 7005 is the PORT number of the named instance SQLEXPRESS. 

Note: Home is the hostname, 7005 is the PORT number of the named instance SQLEXPRESS.

Method 5

The following example shows how to connect to a SQL Server instance using windows authentication and execute a command directly instead of interactive SQL.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>
SQLCMD -Stcp:HOME,7005 -E -Q"Select @@servername"

--------------------------------------------------------------------------------
------------------------------------------------
HOME\SQLEXPRESS

(1 rows affected)

Note: Home is the hostname, 7005 is the PORT number of the named instance SQLEXPRESS. [Refer Fig 1.2]


Fig 1.2

Method 6

The following example illustrates how to connect to a SQL Server instance using windows authentication, execute a command directly on to a database and get the results instead of interactive SQL.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>
SQLCMD -Stcp:HOME,7005 -E -dtest -Q"Select name from emp "
name
--------------------------------------------------------------------------------
--------------------
John Smith
Samantha Carter
Rainbow River
Dancing Doll
Rainbow Dance
Princess Orchid

(6 rows affected)
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>

Note: Home is the hostname, 7005 is the PORT number of the named instance SQLEXPRESS and test is the database name.

Conclusion

Part 1 of this series illustrated a few of the various ways of using the SQL Server 2005 command line utility “SQLCMD”. In future articles, we will discuss more of the SQLCMD utility features.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers