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

February 20, 2007

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)

Click for larger image

Fig 1.1

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers