SQLCMD utility in SQL Server 2005

In one of our previous blog post, we had covered some of the command line utilities in SQL Server 2005. One of such utilities is SQLCMD. This is a replacement for the osql and isql command line utilities that have existed since ages in SQL Server. Those are still available but will be deprecated in the future and it is recommended to use SQLCMD instead. We had also covered the usage of SQLCMD when we explained the DAC feature in SQL Server 2005.

Let’s start a session and go through some of the commands to display how one can make use of SQLCMD.

In prior releases, one could make use of the -L parameter for the osql utility to find out all the SQL Server instances that are available in the environment. The same thing can be done with SQLCMD as well. Figure 1 below shows that execution.

In addition, you can see the different command line options that are available for this utility. Let’s make a connection using Windows Authentication. As you can see in the image below, we connected to the local instance using Windows authentication and then executed the command to list all the databases on that instance and then switched the context to the DECIPHER_TEST database. This is the same behavior as in the case of osql.

One can then execute any query just like one would do in SSMS (SQL Server Management Studio). One does need to use the command separator “GO” to submit a batch of commands (could be one command or more) to the engine for execution. In order to exit out of the execution, you can just type “exit” and enter and it will take you out of the SQLCMD shell and back to the command prompt.

Just like you did with OSQL or ISQL, you can use this command line utility to write up your batch or command files and take in input in the form of a SQL file that contains the commands and can pipe the output to an external file. Example:

sqlcmd /S(local) /E /iC:\DEC_TEST\Test_sqlcmd.sql /oC:\DEC_TEST\output.txt

This utility has a lot of enhancements over the previous osql version. You can refer BOL for a complete list of explanations for the command line options. Here, we will go over some of the important ones:

1) Using an editor:

Using the same example as we used before, suppose that in the interactive mode, I wrote:

select name from sys.databases

and then I want to modify this command. I can then type:

:ED

and upon entering, I will get the default editor.

In case you do not find this editor to be of your liking, you can then change it to say notepad.exe by using this command from the command prompt:

C:\>set SQLCMDEDITOR=notepad.exe

Now, when you do “:ED” from within SQLCMD shell, you will bring that up in the notepad editor. There are other such formating options (setting up satrt-up options using SQLCMDINI, setting up tineouts, column separators etc.) as well that you can play with and set as per your convenience and needs.

2) Variable substitution:

Variables within a T-SQL script can be substituted either in the interactive mode or at the execution time as well using encironment variables. Example:

sqlcmd /S(local) /E /iC:\test_sqlcmd.sql -v T1=”Decipher_test” -v T2=”create_date”

The content of the test_sqlcmd.sql script is:

select $(T2) from sys.databases where name = ‘$(T1)’;

And the output of that execution is:

C:\>sqlcmd /S(local) /E /iC:\test_sqlcmd.sql -v T1=”Decipher_test” -v T2=”create_date”
create_date
———————–
2007-10-13 14:38:01.953

3) Additional sqlcmd commands:

In addition to the T-SQL command executions, there are other commands as well that can be executed from within sqlcmd. We saw the “:ED” one for invoking the editor. Likewise, there are others such as “:Error”, “:Perftrace”, “:XML ON” etc. which are pretty nice. BOL has detailed explanations and examples on those. The :Perftrace one is really powerful as it allows you to put out your statistics (IO, Time, Showplan texts, profile etc.) to a file.

4) Editing SQLCMD scripts in SSMS:

One can even use the SQLCMD mode in SSMS to edit the SQLCMD scripts. You can see this from the image below:

!!sqlcmd /S(local) /E /iC:\test_sqlcmd.sql -v T1=”Decipher_test” -v T2=”create_date”

As you can notice from above, you need to use !! in front of the sqlcmd command in order to run them from SSMS. First you need to switch to the SQLCMD mode in SSMS which you can do either by clicking on the GUI button as shown in the image above (you can also choose to do this via Tools/Options settings).

5) Usage of “:!!”:

We demonstrated that above as well. This command allows one to execute an OS level command on the client without any need to use xp_cmdshell. Example:

C:\>sqlcmd /S(local) /E
1> :!! DIR
Volume in drive C has no label.
Volume Serial Number is DC51-3012

Directory of C:\

01/29/2007 07:34 PM 0 AUTOEXEC.BAT
03/20/2007 08:17 PM <DIR> bin
01/29/2007 07:34 PM 0 CONFIG.SYS
11/17/2006 07:54 AM 231 Default.aspx
01/29/2007 02:22 PM <DIR> dell
01/29/2007 07:37 PM <DIR> Documents and Settings
01/29/2007 02:28 PM <DIR> drivers
09/05/2001 09:00 PM 1,700,352 gdiplus.dll
01/31/2007 06:09 PM <DIR> IBM
01/29/2007 07:46 PM <DIR> Inetpub
08/23/2006 04:26 AM <DIR> privates
12/03/2007 08:26 PM <DIR> Program Files
03/20/2007 08:18 PM 125 project.lgp
03/20/2007 08:17 PM <DIR> rdDownload
03/20/2007 08:17 PM <DIR> rdTemplate
01/29/2007 07:38 PM 1,633 startup_debug.log
01/29/2007 02:12 PM 1,033 summary.htm
12/08/2007 05:29 PM 55 test_sqlcmd.sql
05/14/2007 06:19 PM 21,504 VacationList_DR.xls
01/03/2007 10:06 AM 4,337 Web.config
12/03/2007 08:27 PM <DIR> WINDOWS
01/29/2007 07:35 PM <DIR> wmpub
03/20/2007 08:17 PM <DIR> _DataXMLs
03/20/2007 08:17 PM <DIR> _Definitions
03/20/2007 08:17 PM <DIR> _Images
03/20/2007 08:17 PM <DIR> _Scripts
03/20/2007 08:17 PM <DIR> _StyleSheets
10 File(s) 1,729,270 bytes
17 Dir(s) 1,059,860,480 bytes free

6) Using the SQL Native Client: SQLCMD connects to the SQL Server engine using the .Net SQL Native client as opposed to the ODBC API that was used by osql.

7) DAC (Dedicated Admin Connection): The DAC option is also available via this utility as we had mentioned in one of our previous blog posts here.

8 ) Finally, the usage of “:CONNECT”: From within one single script, one can connect to different SQL Server instances and execute the scripts. This can help a lot for admin and maintenance tasks.

We would highly recommend getting familiar with this wonderful command line utility and start migrating your older scripts to sqlcmd.