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:

:listvar

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
  go
  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)
  :setvar SQLCMDMAXFIXEDTYPEWIDTH 20
  set nocount off
  go
  :setvar SQLCMDMAXFIXEDTYPEWIDTH
  --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.