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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 20, 2007

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

By Muthusamy Anantha Kumar aka The MAK

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



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