Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 17, 2007

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

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM