sqlcmd Utility in SQL Server 2005

Out of the many utilities that SQL Server 2005 has come up with I thought to take a sneak preview of the command line tools that got introduced with SQL Server 2005. Yes, sqlcmd Utility. Even though the intend is to use sqlcmd moving forward rather than the conventional osql command, we will take a quick tour of how we can use sqlcmd utility interactively, execute scripts and more.

Using sqlcmd Interactively

Basically using the SQL Server Management Studio gives you a very satisfying feeling there are loads of interesting ways you can access sqlcmd commandline tool. Get to your SQL Server 2005 box and fire an command prompt. Type sqlcmd. This will use windows authentication and log onto the local machine box. You could also use sqlcmd /? to list all the commandline switches available. To start of with here is a simple query we fired from sqlcmd prompt:

This lists all the available databases on the server. There are also some interesting command options that are available with sqlcmd utility. Try to fire :ServerList from the command window, you will find all the avalable SQL Server's listed out for you. For more on such commands just type :Help /?. A typical output looks like:

Use Exit / Quit to exit the utility and return to the command prompt.

Executing a script

This is one of the primary requirement when we work with command line utilities. When script files need to be executed more often than not I see people using a simple command to execute the script. In our example we will load a Test.sql file with some T-SQL commands. We will execute the same from the sqlcmd prompt.

C:\sqlcmd>sqlcmd -i test.sql

The -i option allows us to specify a script file to execute. In addition to this the -o option can redirect the output to the specified destination file. But this seem to work the same way we used to work before with osql. I realized from the osql prompt we can execute a file like below:

C:\sqlcmd>sqlcmd

  1> :out output.txt

  2> :r test.sql

This behaves exactly the way the previous method works. In addition to the above command you can use the %ERRORLEVEL% variables value when executing from the command. This will return a 0 if the statements inside the files execute to completion without errors.

There are a set of environment variables that are used by the sqlcmd utility. From the sqlcmd prompt to list the same use :ListVar and view the current list. For the above example we would implicitly use the values in variables SQLCMDSERVER, SQLCMDUSER and SQLCOMMANDPASSWORD. If the user is NULL or "" then we use windows authentication.

Using variables with scripts

I know this is the next thing as script executors we would like to see. To define a variable use the $(Variable Name) syntax for definition and use the -v option to pass these variables. Let us take a simple example and create a file with the following contents:

To execute the script with the parameters the final command would look like:

C:\sqlcmd>sqlcmd -i test.sql -o Output.txt -v cols="name,object_id,create_date" tablename="sys.objects"

Here we have suplied a list of column names and the tablename parameters and the output of this script would be redirected to the file Output.txt. You can also declare environment variables for the variables (cols and tablename) and execute the same without the -v switch too.

Conclusion

In this article we took a quick tour of how to use sqlcmd utility under various options. This is just a representative set of ways that we can access this utility. But as we explore more and more into these lesser explored tools we would know where to use each of them.