SQL Server 2005 Command Line Tool “SQLCMD” – Part I

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]

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

Latest Articles