Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:
: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.

Total article views: 17595 | Views in the last 30 days: 21
 
Related Articles
BLOG

SQLCMD – Explicit and Implicit calls – Examples

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

SCRIPT

Export Data in Text File Using SQLCMD in SQL SERVER 2005

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

FORUM

No Editor from Script Task module

No Editor from Script Task module

FORUM

Best Editor To use

Best Editor to use for TSQL

ARTICLE

Deploying Scripts with SQLCMD

Deploying changes to your SQL Servers can be a challenge. Longtime SQL Server expert David Poole bri...

Tags
sql server 2005    
tools    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones