In SQL Server 2005, Microsoft introduced a new command line tool called SQLCMD, which replaced the older tools osql and isql. In this article, we will provide a brief introduction to SQLCMD and discuss its differences from osql and isql. We will also explore some customization options available in SQLCMD.

SQLCMD, osql, and isql

Microsoft had previously announced that isql would be phased out, and indeed, it was not included in SQL Server 2005 RTM. While osql is still included in the binary install of SQL Server 2005, it is expected to be phased out as well. Migration from osql to SQLCMD should be relatively easy, as most of the command line switches are identical. The main difference is that SQLCMD offers additional switches. To see the differences, you can open a DOS command line and type “sqlcmd /?” and “osql /?” to get a general idea of their switches. Most, if not all, of your existing SQL script files should run without any modifications under SQLCMD, making migration a seamless process.

Like osql, SQLCMD supports both batch and interactive modes. Batch mode is primarily used for scripting and automation tasks, while interactive mode is ideal for ad-hoc query analysis. Additionally, SQLCMD can be run within SQL Server Management Studio (SSMS), although there are some limitations in this mode. Certain commands may not work within SSMS, and you can refer to the SQL Server Books Online (BOL) for more information on these limitations.

Customizing SQLCMD

SQLCMD provides several variables that can be customized to suit your needs. To view a full list of available SQLCMD variables, you can use the command “:listvar” when you are in the SQLCMD command line. In this article, we will focus on customizing two settings: sqlcmdini and sqlcmdeditor.

Setting up an initialization script for SQLCMD

You can set the sqlcmdini variable to run a query whenever you launch SQLCMD from the command line interactively. This can be useful for retrieving information such as the version number, edition level, and patch level of the connected SQL Server instance. Here are the steps to set it up:

1. Create a script file, e.g., “c:\work\scripts\SQL\Initialization.SQL”.
2. Customize the script to include the desired statements. For example:

--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 the DOS prompt, type: “set sqlcmdini=c:\work\scripts\SQL\Initialization.SQL”.

Now, whenever you launch SQLCMD interactively, it will automatically run the specified query and display the results. This can be a valuable tool for DBAs to quickly gather information about the connected SQL Server instance.

Customizing the SQLCMD editor

By default, if you type “ed” in SQLCMD, it will invoke a text editor and load the last command you executed into the editor buffer. The default editor is Edit, a command line editor in DOS. However, you can change the default editor to your preferred text editor, such as Edit Plus, TextPad, or even Notepad. This can greatly enhance your productivity and make you feel more in control of your work environment. Here’s how you can change the default editor:

1. Open a DOS prompt.
2. Type “set sqlcmdeditor=vim” (or the name of your preferred editor).
3. Go to SQLCMD, enter and execute a SQL statement, and then type “ed”.
4. Your preferred editor will open with the SQL statement loaded. You can edit it as needed.
5. After you are done editing, save and close the file.

This feature allows you to work in your favorite editor, increasing your productivity and making your SQLCMD experience more enjoyable.

Conclusion

We hope this article has provided you with a general understanding of SQLCMD and its capabilities. In the next installment, we will explore SQLCMD variables in more detail. Stay tuned!