dcsimg
 

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

Tuesday Feb 20th 2007 by Muthusamy Anantha Kumar aka The MAK

Part II of this series illustrates how to use the SQLCMD utility to input Transact SQL scripts to be executed on a SQL Server instance.

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

Home
Mobile Site | Full Site