Using SQL Server 2005 sqlcmd Utility
November 30, 2005
SQL Server 2005 provides some new command line utilities. One such utility is "sqlcmd". The sqlcmd utility is used to run adhoc queries interactively from a command prompt window, or can be used to execute a script containing T-SQL statements. The sqlcmd utility is a great improvement over osql and isql of older releases of SQL Server. In this article, I will explain some of the features this new command line utility brings to administering SQL Server.
Interactively Using the sqlcmd Utility
The sqlcmd utility can be used to run T-SQL commands interactively from a command prompt window. To run the sqlcmd utility interactively you need to start a command shell session, then type "sqlcmd" at the command prompt and hit enter. Running the sqlcmd utility without any options starts an interactive session, where T-SQL commands will be run against the default instance of SQL Server using Windows Authentication. The sqlcmd utility can also be run against different instances of SQL Server by using the "-S" switch or using the ":Connect" sqlcmd command.
When you start the sqlcmd utility interactively the utility displays a "1>" prompt followed by a blinking cursor. To execute a T-SQL command you first type the command and then hit enter. Doing this will display a "2>" prompt and the command you entered will be placed in a buffer known as the statement cache. To execute the T-SQL commands in the statement cache enter the "GO" statement. Multiple lines of T-SQL can be entered, and placed in the statement cache, prior to running them with the "GO" command. To exit out of your interactive session with the sqlcmd utility issue the "EXIT" command.
Below is an example of using the sqlcmd utility interactively:
Here I have run the sqlcmd utility and connected to a SQL Server instance named "SQL2005S1". Once the utility started I entered three different T-SQL commands (declare, select, and print). These three commands where placed in the statement cache, and then when I entered the "go" command they where executed. As you can see, my print statement displayed the information stored in the local variable @version.
Once the GO statement is executed, the statement cache is cleared. If you have made mistakes, or desire not to run the current buffer cache you can clear it by issuing the ":RESET" command.
Another sqlcmd utility feature is, you can edit the commands currently in the statement cache, or the set of commands that were just executed. To do this you use the sqlcmd utility "ED" command. This command will edit the statement cache using the editor identified by the SQLCMDEDITOR environment variable. Here is an example where I have set the SQLCMDEDITOR to "notepad":
As you can see, I set the SQLCMDEDITOR environment variable to "notepad" prior to executing the sqlcmd utility. At the sqlcmd prompt, I then incorrectly typed the "prnt" T-SQL statement, so I issued the "ed" command. Doing this brought up the notepad window. When doing this, notepad will open a file that contains the statements in the statement cache, in my case just the command that contained the typo. Using notepad I can then correct my typo, save the changes and exit. This would take me back the sqlcmd prompt, where it will display my corrected command, and then I can issue the "go" command to execute the corrected command.
Using Scripting Variables in a sqlcmd Script
One of the real values of the sqlcmd utility is the fact that you can build a script that can have variables. This allows you to build T-SQL scripts that can be dynamically changed by variables. The variables in sqlcmd utility are known as scripting variables. Scripting variables can be set in a sqlcmd script, by passed variables using the v switch, or by using an environment variable. To demonstrate let me build a sqlcmd script that uses all three type of scripting variables. Here is a script, which is saved in a file called c:\temp\script_variables.sql
-- Display Environment Variable Print '$(ServerDefault)' -- Connect to ServerDefault :connect $(Server_Default) -- Set Local scripting variable MyTable :setvar MyTable sysobjects -- select top number of records from MyTable, based on TableCount variable select top $(TableCount) name from $(MyTable)
In this script, I use three different variables: ServerDefault, MyTable, and TableCount. The ServerDefault variable is an environment variable, the MyTable variable is a local scripting variable and the TableCount variable is a variable that is set via the v switch. I run this code with the following command:
sqlcmd -ic:\temp\script_variables.sql -vTableCount=1
Here is the output from running this code:
Sqlcmd: Successfully connected to server 'SQL2005S2'. SQL2005S2 name ----------------------------------------------------------------------- all_columns (1 rows affected)
Now I can change the number of records returned from my SELECT statement by running the following command:
sqlcmd -ic:\temp\script_variables.sql -vTableCount=2
Here is the output from this command:
Sqlcmd: Successfully connected to server 'SQL2005S2'. SQL2005S2 name ----------------------------------------------------------------------- sysrowsetcolumns sysrowsets
Using script variable will allow you to build some generic scripts that can easily be modified by setting different variable values. This functionality is a great improvement over how you had to build dynamic scripts using osql or isql. By using scripting variables, you should be able to build generic sqlcmd scripts that can be easily tweaked by scripting variables. One such usage would be to a build a single script that when modified with different scripting variable values could be used to perform the same process on your test and/or production environment.
Using a sqlcmd Startup Script
The sqlcmd utility has the capability to run a startup script each time it is started. This is accomplished by setting the environment variable "sqlcmdini" to the script you want to run when the sqlcmd utility starts. This functionality could be useful if you want to set a number of script variables every time the sqlcmd utility starts. Let me show you an example of using a startup script. Say I have a startup script that looks like this and is stored in c:\temp\sqlcmd.ini:
:setvar defaultserver SQL2005S2 print 'Log on to the Default Server' go :connect $(defaultserver)
Then when I execute the following commands, the sqlcmd utility will print the message "Log on to the Default Server", and then connect to server SQL2005S2:
set sqlcmdini=c:\temp\sqlcmd.ini sqlcmd
Using the Query Editor to build a sqlcmd Script
You can build a sqlcmd utility script using the query editor in the Server Management Studio. To do this you need to enable sqlcmd mode. This can be done by selecting "SQLCMD mode" from the "Query" menu, or by clicking on the "SQLCMD mode" icon on the tool bar as shown in the following screen shot:
By turning on SQLCMD mode, you are allowed to edit a sqlcmd utility script just like any other T-SQL script from the query pane of Server Management Studio. One nice advantage of doing this is you get color coding of the editor to help debug your script as you write it, plus you are allowed to use other editor features like parse your code prior to execution, or reviewing the execution plan.
Issues Regarding Using sqlcmd
The sqlcmd utility uses the OLE DB provider, but the SQL Server Management Studio uses .NET SqlClient when executing sqlcmd scripts from Query pane. Because each of these providers use different default it is possible that the same sqlcmd script could produce different results depending on whether it is executed from the command prompt, or from within the SQL Server Management Studio.
The sqlcmd utility has much new functionality over its predecessor's osql and isql. The Microsoft developers have done a great job in building the sqlcmd utility for SQL Server 2005. No way can I discuss all of the functionality of the sqlcmd utility in a single article. If you are interested in finding out more about the sqlcmd utility, I would suggest you review the sqlcmd topics in SQL Server 2005 Books Online. If you write lots of osql script today in SQL Server 2000, then you will get great pleasure out of the new sqlcmd utility in SQL Server 2005.