SQL Server 2005 - Using the Sqlcmd Utility

SQL Server 2005 - Using the Sqlcmd Utility

In this tutorial you will learn about SQL Server 2005 - Using the Sqlcmd Utility, Running the sqlcmd utility, Sqlcmd rules - Quoted Strings, Code Page Conversion Rules and Interactive sqlcmd Example.

The interactive execution of T-SQL statements and scripts is enabled by the Win32 Command prompt utility called sqlcmd utility. This utility can be used to build scripts to be run by sqlcmd users or to interactively enter T-SQL statements into the utility in a manner that is similar to working with the command prompt window. Users can submit a sqlcmd job as a single T-SQL statement for execution or point the utility to a text file that contains the required T-SQL statements. The output is then displayed in the window or in a text file.

Running the sqlcmd utility

Open the command prompt window and type sqlcmd at the prompt. A number of switches can be used ot specify the options of the utility.

The –s switch is a server switch that identifies the instance of the SQL Server to which the utility is to be connected. –E,-U and –P specify the credentials sqlcmd uses to connect (the –E is default). Input switches are –Q, -q and –i and output switches are –o.

When the sqlcmd utility is executed without input files or queries the sqlcmd connects to the default instance of MSSQL Server and displays the 1> called the sqlcmd prompt. The1 signifies the first line of the transact sql statement to be entered. In other words in the sqlcmd prompt the user can type sqlcmd commands and T-SQL statements such as GO and EXIT. Each T-SQL statement is buffered in a statement cache and are sent to the SQL server after the GO statement followed by the Enter key. The sqlcmd can be terminated using the EXIT command. The statement cache can be cleared by typing RESET. ^C can be used to stop the execution of a statement cache after the GO command is given.

Sqlcmd rules:

Quoted Strings

When Characters are enclosed in quotes, no preprocessing is done. SQL Server treats quotes inserted into a string as consecutive quotes as a single quote. Moreover, scripting variables are not expanded when they appear within a string. When strings span multiple lines they still treated as a single string.

Code Page Conversion Rules

If code pages are specified sqlcmd uses the code page both for input and output files. If the input file is a Unicode file, no conversion is attempted. The big-endian and little-endian Unicode input files are automatically recognized. If the –u option is used the output will default to little-endian. When no output file is specified the output page displayed will be the console page. If multiple input files are specified, they are all assumed to be the same code page. Unicode and non Unicode files can be mixed.

Interactive sqlcmd Example

1. Open the command prompt window
2. Type sqlcmd at the command prompt.
3. Type USE EXFORSYS and press enter and then enter GO and press enter.
4. You will get a display “Changed database context to ‘Exforsys’
5. After the output is generated, sqlcmd once more displays the 1> prompt on which the user can type EXIT and exit the utility. You can again type EXIT to exit the command prompt.

Database script files can also be executed using the sqlcmd utility. Database script files contain a mix of T-SQL statements and sqlcmd commands and scripting variables. Sqlcmd runs through the input file without a pause and does not wait for any user inputs.

Database script files can be built in different ways. The first of these methods is to write T_SQL statements in the Microsoft SQL Server Management Studio and save the contents to the Query window as a script file. The second method is to create a text file containing a T-SQL statement using a text editor.

To run a script file using sqlcmd open Notepad and type T_SQL statements as under:

USE Exforsys
GO
SELECT TOP(5) STUDENTID, FName, LName
FROM Student.Contact
GO

Save the file as ExforsysScript.sql in a folder under the C:\. Now run the following command from the command prompt and place the output file in the same folder as Exforsysoutput.txt. Now view the contents of the file. You will see the data in the following format:(The data below is an illustration and not derived from an actual database)

Changed database context to ‘Exforsys’

StudentID ........FName ........LName
........1 ................James .........Collen
........2 ................Abba .............Raymond

A number of variables can be used in scripts and are known as scripting variables. These variables provide the user with flexibility of using a single script in multiple scenarios. These scripting variables can be defined explicitly using the setvar command or implicitly using sqlcmd switches. All variables defined using setvar command take precedence over variables defined as environment variables.

If sqlcmd is started with a switch that has a related sqlcmd variable, the variable is set implicitly to the value specified using the switch. For instance if sqlcmd is started with the -l switch the variables are implicitly set to SQLLOGINTIMEOUT variable.

While naming the scripting variables certain rules and conventions are followed. The variable must not contain white space characters and they must not have the same form as a variable expression. Also, variable names must be quoted if the string values contain spaces. If the variable names contain quotation marks they must be escaped. It must be remembered that scripting variable names are case sensitive and when no value is assigned to a sqlcmd environment variable the same is removed.