Among the people I work with, I'm known as a command line guy in a Windows world.  Yes, I use Start | Run to open cmd about 100 times a day, and I usually have a few open on my desktop.  But I never really took to ISQL or OSQL when working with SQL Server.  Oh, don't get me wrong, I use Query Analyzer 10:1 over SEM (some things are just faster in the GUI, as much as I hate to admit it). 

One tool in SQL Server 2005 may change me from the GUI completely (notice I said may), and that's SQLCMD.  I became familiar with the tool while working on the presentation material for the Detroit Launch Event.  Until then, it was but a bullet point on some marketing slides. 

In true Letterman fashion , here are my Top 10 Reasons why SQLCMD could become my new favorite tool. 

1. Extensive variablization.  Environment variables and declared variables can be used in various places in the script, including to change database (E.G. USE ($DB) ) and changing the server you are connected to (more on this next).

2. :CONNECT.  From within a single script, you can connect to multiple SQL Server instances and execute commands.  This allows for a great deal of automation possibilities, such as backup from one server, restore to another server.  Also, you could execute a configuration change script on all servers in production.  That's extremely powerful.

3. :!!.  This command allows you to execute an OS command on the client without having to connect and use xp_cmdshell.

4. SQL.INI.  This ini file allows you to set default parameters and even create a default query to execute on the server as soon as you connect.  This helps you to automate your connection properties and to some degree your environment in command line mode

5. SQLCMD Mode in SSMS.  Yeah, that's write, I'm mentioning the GUI in this post.  Essentially, if you build your SQLCMD script is SSMS, you have an IDE for development with all the benefits of command line.  It's the best of both worlds! You can set this in Tools | Options or click the button with the Red ! in SSMS (Not the Execute button, the other one).

6. Error Handling. :Error :On Error :EXIT with the -b and -v switches.  There's pretty much all you need in some combination of these to gracefully deal with run-time problems.  That is so much better than piping out and digging through log files! 

7. :PerfTrace.  This feature lets you put your statistics (IO, TIME, ShowPlan) out to a separate file for later viewing as so not to clutter the results file output.

8. :XML ON - Put all out put to XML format

9. Performance. SQLCMD connects with the .NET SQLCLIENT OLEDB Provider instead of ODBC, which is a much better performer

10. Remote Dedicated Admin Connection (DAC).  If you use the -A switch, you can get into a server that might otherwise be hung.  You can kill an offending process and restore order to the universe - without a reboot!

You'll notice that there are a lot of new commands, almost a sub-language for SQLCMD.  It might take a little while to learn these new features, but I think it will be well worth it.

So now Oracle DBAs can't kick SQL*Plus in our faces anymore.  We have a tool that easily competes and may be even better.  Have you looked at SQLCMD yet?  Are there other features of this tool you think are important? Tell me what you think.

Jon Baker