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.