Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Introduction to SQLCMD

By Haidong Ji,

In SQL Server 2005, Microsoft introduced a new command line tool, SQLCMD, as a replacement for osql and isql. In a series of articles, I will give you a brief introduction to this versatile and powerful tool. In this installment, I will talk about differences among SQLCMD, osql, and isql. I will also give you some pointers on SQLCMD customization.

SQLCMD, osql, and isql

Microsoft has announced before that isql was on its way out. Sure enough, isql is not included in SQL Server 2005 RTM. osql eventually will be phased out also, although it is still included in SQL Server 2005 binary install. With the historical SQL Server release cycle in mind, maybe osql will bid us adieu in 2010;)

Migration to SQLCMD should be easy, since most of the command line switches are identical. The only difference would be that more switches are added in SQLCMD. To see the differences, open a DOS command line, and type sqlcmd /? and osql /?, this will give you a general idea of switches of them.

Most, if not all, your existing SQL script files should run as is under SQLCMD, without any modifications. So migration should not be a problem.

Like osql, SQLCMD has batch and interactive modes. You use batch mode mainly for scripting and automation tasks, whereas interactive mode is for ad-hoc query analysis.

In addition, you can run SQLCMD within SQL Server Management Studio (SSMS). There are some limitations on that. For example, certain commands will not within SSMS. More information on that can be obtained through BOL.

Setup an initialization script for SQLCMD

SQLCMD provides a few variables you can customize to suit your needs. To see a full list of available SQLCMD variables, when you are in SQLCMD command line, type:
will give you a list of all available variables. I will show you how to customize 2 settings here: sqlcmdini and sqlcmdeditor.

You can set sqlcmdini so that sqlcmd would run a query whenever you launch it from the command line interactively. For example, you can let it run a query and return some useful information like version number, edition level, and patch level. All these are great to know for a DBA. Here are steps to set it up:

  • Create the script file. Let’s call it c:\work\scripts\SQL\Initialization.SQL. For example, you can put statements below, customize it to suit your needs:
    --Begin script
    set nocount on
    print 'You are connected to ' + rtrim(CONVERT(char(20), SERVERPROPERTY('servername'))) 
    + ' (' + rtrim(CONVERT(char(20), SERVERPROPERTY('productversion'))) + ')' + ' ' 
    + rtrim(CONVERT(char(30), SERVERPROPERTY('Edition'))) + ' ' 
    + rtrim(CONVERT(char(20), SERVERPROPERTY('ProductLevel'))) + char(10)
    set nocount off
    --End script
  • In DOS prompt, type:
    set sqlcmdini=c:\work\scripts\SQL\Initialization.SQL
    Here are some sample results:
    C:\Documents and Settings\Haidong\sqlcmd
    You are connected to MARIALAPTOP (8.00.194) Enterprise Evaluation Edition RTM
    1. exit
    C:\Documents and Settings\Haidong\sqlcmd -S.\ssistest
    You are connected to MARIALAPTOP\SSISTEST (9.00.1399.06) Standard Edition RTM
    1. exit

Customize SQLCMD editor

In SQLCMD, if you type ed, it will invoke a text editor and put the last command you run in the editor buffer. The default editor is Edit, a command line editor of DOS.

This behavior is very similar to Oracle SQL Plus, by the way. The difference is that in SQL Plus, you can use / to run the command after you finished editing, whereas in SQLCMD you still need to type go for it, well, to go;)

Anyway, the really neat thing is you can change the default editor to whatever you like. I know many DBAs / developers have their own preferred editors, like Edit Plus, TextPad, or even plain old Notepad. This is exciting because if you work in your favorite editor, you feel you are in control and your productivity increases. For me, I have changed the default editor to VIM, a clone of VI.

This is how you do it:

  • Open a DOS prompt;
  • Type set sqlcmdeditor=vim, or notepad or whatever;
  • Go to sqlcmd, enter and execute a sql statement, then type ed;
  • Your editor of choice opens. Edit away, after you are done, save and close;
I am very happy to find this feature. It is a real productivity enhancer.

I hope you have some general understanding of SQLCMD after reading this article. I will write about SQLCMD variables in the next installment.

Total article views: 17838 | Views in the last 30 days: 11
Related Articles

Installing SQLCMD

For anyone who wasn’t aware SQLCMD is a command line tool for running T-SQL statements and scripts. ...


SQLCMD – Explicit and Implicit calls – Examples

The scripting variables can be defined explicitly by using the setvar command or implicitly by using...


Export Data in Text File Using SQLCMD in SQL SERVER 2005

This script export the data in text file using sqlcmd command.


No Editor from Script Task module

No Editor from Script Task module


SQLCMD and Batch File magic

Execute scripts in a folder with a single click using SQLCMD and a batch file.

sql server 2005