SQL Server 2005 Command Line Tool “SQLCMD” – Part III

In Part I and part II we saw a few ways of using the SQL Server 2005 SQLCMD command line utility. In Part III of this series, I am going to illustrate how to use scripting variables, startup scripts and variable substitution.


Scripting variables come in handy when you want to set up the environment before executing the SQLCMD command line utility. These scripting variables allow you do variable substitution and help in calling the startup script when the SQLCMD command line utility is initiated. Let me illustrate all these features.


Method 13


Using Text Editor inside SQLCMD execution:


Execute the following command at the MS-DOS command prompt as shown below. The SQLCMD command line utility provides the new feature of opening the text editor to edit the command that you typed recently. The command for opening the text editor is :ED


Let us see how we could open the default TEXT editor and see how to change the default TEXT editor.


Type the following command at the MS-DOS prompt and execute the SQL Statement as shown below.

sqlcmd -SHOME
1> select name from sys.sysdatabases
2> go
name
——————————————————————————–
————————————————
master
tempdb
model
msdb
Admin

(5 rows affected)


Note: HOME is the sql server instance.


If we want to change the script that I typed from

select name from sys.sysdatabases

to

select name as [Data Base Name] from sys.sysdatabases

I could type the following command.

:ED

This opens the default editor EDIT.com and copies the command to the editor window as shown below. [Refer Fig 1.0 ]




Fig 1.0


If I want to change the editor from the default editor EDIT.com to Notepad.exe, I could change the value of the scripting variable SQLCMDEDITOR.


Close the MS-DOS window, then open a new MS-DOS window and execute the following command.

set SQLCMDEDITOR=notepad.exe
C:\>set SQLCMDEDITOR=notepad.exe

C:\>sqlcmd -SHOME
1> select name from sys.sysdatabases
2> go
name
——————————————————————————–
————————————————
master
tempdb
model
msdb
Admin

(5 rows affected)
1>


Now execute the following command to initiate the editor. It opens the sql script in notepad. Fig 1.2

:ED



Fig 1.2


Method 14


Let us use some of the commonly used scripting variables to set the environment for the SQLCMD command line utility. Refer Fig 1.3

set SQLCMDLOGINTIMEOUT=2
set SQLCMDCOLSEP=!
set SQLCMDCOLWIDTH=10
set SQLCMDPACKETSIZE=4096
set SQLCMDEDITOR=Notepad.exe

C:\>set SQLCMDLOGINTIMEOUT=2
C:\>set SQLCMDCOLSEP=!
C:\>set SQLCMDCOLWIDTH=10
C:\>set SQLCMDPACKETSIZE=4096
C:\>set SQLCMDEDITOR=Notepad.exe
C:\>SQLCMD -SHOME
1> select “Test” as DBname, “Test2” DBID
2> go
DBname!DBI
D
——!—

Test !Tes
t2
(1 rows affected)
1>



Fig 1.3


Note: In the result window you can see the column separator “!” instead of the default blank space.


Method 15


We can use the scripting variable SQLCMDINI for setting up the startup script for the SQLCMD command line utility, so that whenever the SQLCMD is executed it runs the script defined in SQLCMDINI.


Let us create the following script, c:\mysetup.sql with the following Transact SQL statements. Fig 1.4

set quoted_identifier off
set rowcount 2



Fig 1.4


Now run the SQLCMD command as described below. Refer Fig 1.5

C:\>set SQLCMDINI=c:\mysetup.sql
C:\>SQLCMD -SHOME
1> select name from sys.sysdatabases
2> go
name
——————————————————————————–
————————————————
master
tempdb

(2 rows affected)
1>




Fig 1.5


Notice that the result set is limited to 2 rows since the SQLCMD command line utility has executed the startup script mysetup.sql.


Method 16


Variable substitution comes in very handy when you want to make the result dynamic, based on the values passed.


Let us create C:\test.sql as shown below.

select dbid as $(ColumnName1), name  as $(ColumnName2) from sys.sysdatabases



Fig 1.6


Now let us execute the SQLCMD command line utility using the input and variable substitution option as shown below.

sqlcmd -SHOME -ic:\test.sql -v ColumnName1=”[Database Name]” -v ColumnName2=”[Database Id]”
Results
C:\>sqlcmd -SHOME -ic:\test.sql -v ColumnName1=”[Database Name]” -v ColumnName2=
“[Database Id]”
Database Name Database Id
————- ——————————————————————
————————————————————–
1 master
2 tempdb
3 model
4 msdb
5 Admin

(5 rows affected)
C:\>


Note: the variables ColumnName1 and Columname2 were substituted by the passed value. Refer Fig 1.7




Fig 1.7


Method 17


These variables can be substituted by environment variables as well as user
defined environment variables.


Let us create an environment variable test.

Set TEST=MYDataBaseName

Now let us execute the SQLCMD command line utility as shown below.

sqlcmd -SHOME -ic:\test.sql -v ColumnName1=”[%TEST%]” -v ColumnName2=”[%date%%time%]”
C:\>Set TEST=MYDataBaseName
C:\>sqlcmd -SHOME -ic:\test.sql -v ColumnName1=”[%TEST%]” -v ColumnName2=”[%date
%%time%]”
MYDataBaseName Tue 02/27/200714:24:11.48
————– —————————————————————–
—————————————————————
1 master
2 tempdb
3 model
4 msdb
5 Admin

(5 rows affected)
C:\>




Fig 1.8


Note: Here %date% and %time% are system environment variables and TEST is a user defined environment variable


Method 18


Since variable substitution literally substitutes the values, we could expand the variables by adding the proper additional syntax to the SQLCMD.


Execute the following command as shown below.

sqlcmd -SHOME -ic:\test.sql -v ColumnName1=”DatabaseID” -v ColumnName2=”[%TEST%], ‘%date%%time%’
as DateStamp “

C:\>sqlcmd -SHOME -ic:\test.sql -v ColumnName1=”DatabaseID” -v ColumnName2=”[%TEST%], ‘%date%%time%’
as DateStamp ”
DatabaseID MYDataBaseName
DateStamp
———- —————————————————————————————–
————————————— ————————-
1 master
Tue 02/27/200714:26:39.56
2 tempdb
Tue 02/27/200714:26:39.56
3 model
Tue 02/27/200714:26:39.56
4 msdb
Tue 02/27/200714:26:39.56
5 Admin
Tue 02/27/200714:26:39.56
(5 rows affected)



Fig 1.9


Note: On the fly, we created a column Datestamp with the current date and time using the same scripting variable ColumnName2. We passed multiple values to the same scripting variable ColumnName2.


Conclusion


Part III of this series illustrates how to use scripting variables, startup scripts and variable substitution.


» See All Articles by Columnist MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles