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.
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]
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 FilesMicrosoft SQL Server90ToolsBinn>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 FilesMicrosoft SQL Server90ToolsBinn>
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 FilesMicrosoft SQL Server90ToolsBinn>
SQLCMD -SHOMESQLEXPRESS -Usa -P******
1> select @@servername
2> Select @@version
3> go
——————————————————————————–
————————————————
HOMESQLEXPRESS
(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 FilesMicrosoft SQL Server90ToolsBinn>
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 FilesMicrosoft SQL Server90ToolsBinn>
SQLCMD -Stcp:HOME,7005 -Usa -P********
1> select @@servername
2> select @@version
3> go
——————————————————————————–
————————————————
HOMESQLEXPRESS
(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 FilesMicrosoft SQL Server90ToolsBinn>
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 FilesMicrosoft SQL Server90ToolsBinn>
SQLCMD -Stcp:HOME,7005 -E -Q”Select @@servername”
——————————————————————————–
————————————————
HOMESQLEXPRESS
(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 FilesMicrosoft SQL Server90ToolsBinn>
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 FilesMicrosoft SQL Server90ToolsBinn>
Note:
Home is the hostname, 7005 is the PORT number of the named instance SQLEXPRESS
and test is the database name.
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
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
Leave a Reply