Developer Product Briefs

Tricks for Processing SQL Scripts

Discover how SQL Server 2005's SQLCMD tool can enhance your scripting processes.

• By Josef Finsel
• 05/01/2006

Tricks for Processing SQL Scripts
Discover how SQL Server 2005's SQLCMD tool can enhance your scripting processes.
by Josef Finsel

May 1, 2006

SQL Server 2005's Management Studio offers lots of new functionality, but you can't always use a GUI. Sometimes you need to run a script from the command line, especially for an upgrade or install script. Previous versions of SQL Server had two command-line utilities: ISQL, which uses DB-Library; and OSQL, which uses an ODBC connection. SQL Server 2005 replaces these two utilities with SQLCMD. This new tool provides a couple of useful tricks that help you process SQL scripts.

All of the SQL Server utilities, including Query Analyzer, recognize certain commands that are not part of Transact-SQL. (Perhaps the best known is GO, which tells the utility to send a batch of commands to SQL Server.) The old command-line interfaces recognized two commands that are useful when using the utility interactively: ED and RESET. ED opens up an editor, and RESET clears any statements made since the last GO. With SQLCMD, however, you have a number of new commands for writing scripts that are more flexible than those used by ISQL and OSQL.

The most useful of these are the command that allows you to access variables, and the :R command that allows you to execute a sub script. For example, you want to process update scripts for a product that has two databases, the production database and a data warehouse. These databases can be named when installed so each existing system is unique. Using the old command-line utilities, you would have to write two scripts and run them individually with parameters. This would allow you to change the database connection between scripts, while also checking for errors between scripts. With SQLCMD, you can run one script and simply exit when you encounter an error.

First you must define an environment variable; in this example, it's called RootDatabaseName. Then run SQLCMD, which will use the variable to change databases. Here is a simple command file for you to run:

SET RootDatabaseName=AdventureWorks
sqlcmd -E -S localhost\sql2005 -i script1.sql

Here is the SQL script for you to run after the command file executes:

USE $(RootDatabaseName)
-- Execute update commands for the production database
USE $(RootDatabaseName)DW
-- Execute update commands for the production database

When the SQL script executes, it simply changes databases twice, assuming the AdventureWorks and AdventureWorksDW databases are on your server. If you execute a production installation script, the server name and root database names are passed into the command script as parameters.

This process is helpful for small scripts, but large scripts can be unwieldy. You must call the command utility multiple times and check for errors between each call, which introduces complexity. Fortunately, the :R command loads a file of SQL statements within a script. Then you can replace each of the commented-out lines in the sample script with :R UpdateScriptName.sql to break the scripts into manageable and logical chunks.

While SQLCMD scripting offers more efficiency and accuracy, you should be aware of a couple caveats when using this technique. First, while you can execute a SQL command with the value of an environmental variable, you cannot populate an environmental variable from a SQL command. Second, if you use :SETVAR to define the value of an environmental variable, it changes only the variable within the script. The variable will retain its original value, as demonstrated in the Script2 command file and SQL script.

Despite these obstacles, SQLCMD is a powerful replacement for the SQL command-line utilities and one that will make life much easier for developers.

About the Author
Josef Finsel is an author, speaker, computer consultant, and programmer specializing in .NET and SQL Server. He wrote The Handbook for Reluctant Database Administrators . Check out Josef's Web site at www.finsel.com.