In Part I we saw a few ways of using the SQL
Server 2005 SQLCMD command line utility. In Part
II of this article, I illustrate how to use transact SQL files and generate
output.
Method 7
The SQL
Server 2005 command line tool, SQLCMD, can be used to input Transact SQL
scripts to be executed on a SQL Server instance. Let us create a C:\Test.sql
file with the following code. Refer Fig 1.0
Select @@Servername
Go
Select @@version
Go
Select Top 3 name from master..sysdatabases
Go
Fig 1.0
Execute
the following command at the MS-DOS command prompt as shown below. You will get
the following results.
SQLCMD -Stcp:HOME,7005 -E –i C:\Test.sql
Results
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>SQLCMD -Stcp:HOME,7005 -E -i
C:\Test.sql
--------------------------------------------------------------------------------
------------------------------------------------
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)
name
--------------------------------------------------------------------------------
------------------------------------------------
master
tempdb
model
(3 rows affected)
Method 8
The SQL
Server 2005 command line tool SQLCMD output can be formatted either in FIXED
length column or in a variable length column. Let
us see how to format the output in a variable length.
Execute
the following command at the MS-DOS command prompt as shown below. You will get
the following results
SQLCMD -Stcp:HOME,7005 -E -Q"Select dbid,name,status,right(filename,
charindex('\',reverse(rtrim(filename)))-1) from master..sysdatabases" -y20
Results
dbid name
status
------ -----------------------------------------------------------------------------------------------------------------
--------------- ----------- --------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
1 master
65544 master.mdf
2 tempdb
8 tempdb.mdf
3 model
65544 model.mdf
4 msdb
65544 MSDBData.mdf
5 test
1073807369 test.mdf
(5 rows affected)
Note: -Y is for Fixed column length
and –y is for variable column length.
Method 9
SQL
Server 2005 command line tool SQLCMD output can be formatted either in FIXED
length column or in a variable length column. Let us see how to format the
output in fixed length.
Execute
the following command at the MS-DOS command prompt as shown below. You will get
the following results. Refer Fig 1.2.
SQLCMD -Stcp:HOME,7005 -E -Q"Select dbid,name,status,right(filename,
charindex('\',reverse(rtrim(filename)))-1) from master..sysdatabases" -Y20
Results
dbid name status
------ -------------------- ----------- --------------------
1 master 65544 master.mdf
2 tempdb 8 tempdb.mdf
3 model 65544 model.mdf
4 msdb 65544 MSDBData.mdf
5 test 1073807369 test.mdf
(5 rows affected)
Fig 1.2
Note: -Y is for Fixed column length
and –y is for variable column length.
Method 10
The
SQLCMD utility can be used to input Transact SQL scripts to be executed on a
SQL Server instance. The output can be stored in an output file. Let us create
a C:\Test2.sql file with the following code. Refer Fig 1.3.
Select dbid,name,status,right(filename,charindex('\',reverse(rtrim(filename)))-1)
as MDF from master..sysdatabases
Fig 1.3
Execute
the following command at the MS-DOS command prompt as shown below. Refer Fig
1.4.
SQLCMD -Stcp:HOME,7005 -E -i c:\test2.sql >C:\test2output.txt
Fig 1.4
The
output is redirected to C:\test2output.txt. Refer Fig 1.5.
dbid name
status MDF
------ -------------------------------------------------------------------------------------------
------------------------------------- ----------- ------------------------------------------------
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
----------------
1 master
65544 master.mdf
2 tempdb
8 tempdb.mdf
3 model
65544 model.mdf
4 msdb
65544 MSDBData.mdf
5 test
1073807369 test.mdf
(5 rows affected)
Results
Fig 1.5
Method 11
The
SQLCMD utility can be used to input Transact SQL scripts to be executed on a
SQL Server instance and the output can be stored in a output file, in a fixed
length column format. Let us create a C:\Test2.sql file with the following
code. Refer Fig 1.6.
Select dbid,name,status,right(filename,charindex('\',reverse(rtrim(filename)))-1)
as MDF from master..sysdatabases
Fig 1.6
Execute
the following command at the MS-DOS command prompt as shown below. Refer Fig
1.7.
SQLCMD -Stcp:HOME,7005 -E -i c:\test2.sql –Y20 >C:\test2output.txt
Fig 1.7
The
output is redirected to C:\test2output.txt. Refer Fig 1.8.
dbid name status MDF
------ -------------------- ----------- --------------------
1 master 65544 master.mdf
2 tempdb 8 tempdb.mdf
3 model 65544 model.mdf
4 msdb 65544 MSDBData.mdf
5 test 1073807369 test.mdf
(5 rows affected)
Fig 1.8
Method 10
and 11 used a conventional method of redirecting the output to a file.
Method 12
The
SQLCMD utility can be used to input Transact SQL scripts to be executed on a
SQL Server instance with the stored in an output file in a fixed length column format.
Let us create a C:\Test2.sql file with the following code.
SQLCMD -Stcp:HOME,7005 -E -i c:\test2.sql -Y20 -o C:\test2output.txt
The
output is redirected to C:\test2output.txt. Refer Fig 1.9.
dbid name status MDF
------ -------------------- ----------- --------------------
1 master 65544 master.mdf
2 tempdb 8 tempdb.mdf
3 model 65544 model.mdf
4 msdb 65544 MSDBData.mdf
5 test 1073807369 test.mdf
(5 rows affected)
Fig 1.9
Conclusion
Part II
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
» 
Comment and Contribute
