SQL Server 2005 Administration: sqlcmd

 

osql and sqlcmd switches

When SQL Server 7 was released, osql was introduced and isql was deprecated. SQL Server 2005 provides the new sqlcmd tool, deprecates osql, and eliminates isql. You can still run legacy osql scripts on SQL Server 2005, but as you can see here, you’ll want to take advantage of the new functionality offered by sqlcmd. Making the transition to sqlcmd is easy because it uses the same syntax as osql in most cases. The differences are shown below:

osql 
                sqlcmd


                     [-A dedicated admin 
                     connection]
[-D ODBC DSN name]
                     [-f   :
                     [,o:] ]
                     [-L[c] list servers[clean
[-L list servers]    output] ]
[-n remove 
numbering]
[-O use Old ISQL 
behavior]
[-p print            [-p[1] print statistics[colon 
statistics]          format] ]
                     [-R use client regional
                     setting]
[-r msgs to          [-r[0|1] msgs to stderr]
stderr]
                     [-u unicode output]
                     [-v var = "value"...]
                     [-W remove trailing spaces]
[-w columnwidth]     [-w screen width]
                     [-Y fixed length type display
                     width]
                     [-y variable length type
                     display width]
                     [-Z new password and exit]
                     [-z new password]

Dedicated Administrative Connection

Of particular interest is the -A switch to establish a Dedicated Administrative Connection (DAC) to SQL Server 2005. It allows a member of the sysadmin role to administer a malfunctioning SQL Server that isn’t accepting connections. The DAC uses TCP/IP and can connect to both local and remote servers. Either Windows integrated or SQL Server authentication can be used.

C:/>sqlcmd -A

1> select blocked from sysprocesses where blocked != 0

2> go

blocked

-------

     55
 


(1 rows affected)

1> kill 55

2> go

1>

sqlcmd : commands

The sqlcmd switches, like osql switches, are limited to establishing the SQL Server connection context. There are additional sqlcmd commands which can be used after a sqlcmd session is started. These commands, some of which exist as undocumented osql commands, are summarized below:

:r filename

:ServerList

:List

:Listvar

:Error filename | STDOUT | STDERR

:Out filename | STDOUT | STDERR

:Perftrace filename | STDOUT | STDERR

:Connect server[/instance] [timeout] [user_name[password] ]

:On Error [exit | ignore]

:SetVar variable value

:Help

:XML ON | OFF

The following example uses :serverlist to get a list of servers, and then uses :connect to connect to the default instance and a named instance. An error condition is forced by passing a nonexistent server name to :connect. The first time the error occurs, the output is seen in the sqlcmd output. The :error command is used before second time the error is forced, so no error output is seen because it is written to the error.txt file instead.

C:/>sqlcmd

1> :serverlist
 


Servers:

   SQL2000

   SQL2005

1> :connect sql2005

Sqlcmd: Successfully connected to server 'sql2005'.

2> :connect sql2005/dbazine

Sqlcmd: Successfully connected to server 'sql2005/dbazine'.

3> :connect nonexistent

Named Pipes Provider: Could not open a connection to SQL Server

Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connection.

Sqlcmd: Error: Microsoft SQL Native Client : Timeout expired.

4> :error error.txt

4> :connect nonexistent

5> 

At the risk of stating the obvious, it’s important to understand that within one script file, you can have connections to different instances which can be on the same or different servers. Each connection can send its error messages to different files and its output to still other different files. Such highly flexible scripts can be implemented using either hardcoded values or by using variables, which are covered in the next section.

sqlcmd variables

Another powerful feature of sqlcmd is the ability to use variables. To demonstrate this, let’s begin with a simple query that uses sqlcmd variables instead of hardcoded column and table names

select $(col1), $(col2) from $(tab)

C:/>sqlcmd
1> use adventureworks
2> go
1> :setvar col1 name
1> :setvar col2 groupname
1> select $(col1), $(col2) from humanresources.department
2> go
name                       groupname
-------------------------- ------------------------------------
Engineering                Research and Development
Tool Design                Research and Development
Sales                      Sales and Marketing
   .
   .
   .
Shipping and Receiving     Inventory Management
Executive                  Executive General and Administration
(16 rows affected)

The previous example, although instructive, probably doesn’t illustrate the value of using variables as well as the next example. The query is saved in a text file named select.sql. By using the –i switch that osql also supports, the select.sql file’s contents are passed as input to sqlcmd. The –d switch is used to specify the database. Using sqlcmd’s –v switch allows variables to be passed from the command line to sqlcmd:

C:/>sqlcmd -i select.sql -d adventureworks -v col1="name" col2="groupname"
tab="humanresources.department"

name                       groupname
-------------------------- ------------------------------------
Engineering                Research and Development
Tool Design                Research and Development
Sales                      Sales and Marketing
   .
   .
   .

Support for variables in sqlcmd also includes support for environment variables. By using the SQLCMDDBNAME environment variable, the previous command can be modified so that the –d switch is not needed:

C:/>set sqlcmddbname=adventureworks
C:/>sqlcmd -i select.sql -v col1="name" col2="groupname"
tab="humanresources.department"

name                       groupname
-------------------------- ------------------------------------
Engineering                Research and Development
Tool Design                Research and Development
Sales                      Sales and Marketing
   .
   .
   .

Similarly, the variables can be set using environment variables, which enable the following syntax to be used:

C:/>set sqlcmddbname=adventureworks

C:/>set col1=name

C:/>set col2=groupname

C:/>set tab=humanresources.department

C:/>sqlcmd -i select.sql

The environment variables, except for the database name, can be set inside the select.sql input file using :setvar. When using :setvar, sqlcmddbname is treated as readonly and cannot be set. Here are the new contents of the select.sql input file:

:setvar col1 name

:setvar col2 groupname

:setvar tab humanresources.department

use adventureworks

go

select $(col1), $(col2) from $(tab)

Alternatively, a regular variable could be used for setting the database:

:setvar col1 name

:setvar col2 groupname

:setvar tab humanresources.department

:setvar dbname adventureworks

use $(dbname)

go

select $(col1), $(col2) from $(tab)

It is possible to split the commands into two separate files, one file for setting the environment, and other file for the T-SQL commands. Remove all of the :setvar commands from select.sql and put them in a separate file called init.sql. The init.sql file is used with the SQLCMDINI environment variable command as shown below:

C:/>set sqlcmdini=init.sql
C:/>sqlcmd -i select.sql
Changed database context to 'AdventureWorks'.
name                       groupname
-------------------------- ------------------------------------
Engineering                Research and Development
   .
   .
   .

The following list summarizes the environment variables that can be used instead of sqlcmd command line switches.

switch       environment variable

-a        SQLCMDPACKETSIZE
-d        SQLCMDDBNAME
-H        SQLCMDWORKSTATION
-h        SQLCMDHEADERS
-l        SQLCMDLOGINTIMEOUT
-m        SQLCMDERRORLEVEL
-P        SQLCMDPASSWORD
-S        SQLCMSSERVER
-s        SQLCMDCOLSEP
-t        SQLCMDSTATTIMEOUT
-U        SQLCMDUSER
-w        SQLCMDCOLWIDTH

When writing batch scripts, it is better to use environment variables instead of hardcoded values. In particular, using SQLCMDUSER and SQLCMDPASSWORD environment variables eliminates the security risk of hardcoding usernames and passwords in scripts that use the –U and –P switches.

SQL Server Management Studio SQLCMD Mode

You can use SQL Server Management Studio’s SQLCMD mode to take advantage of syntax color coding while developing and testing sqlcmd scripts. SQLCMD mode is off by default. Turn it on by clicking the SQLCMD icon or by using the Query menu as shown in the following screen capture:

It is important to understand that sqlcmd uses OLEDB and SQL Server Management Studio uses the .NET SqlClient. If you test sqlcmd scripts in the SQL Server Management Studio, it is possible to obtain different results than when the scripts are actually run through sqlcmd.