Running Transact-SQL Statements Interactively Using sqlcmd 

You can use the sqlcmd utility interactively to execute Transact-SQL statements in a command prompt window. To interactively execute Transact-SQL statements using sqlcmd, run the utility without using the -Q, -q, or -i switches to specify any input files or queries. For example:

sqlcmd -S ComputerName\InstanceName

When it is executed without input files or queries, sqlcmd connects to the specified instance of Microsoft SQL Server and then displays a new line with a 1> followed by a blinking underscore, called the sqlcmd prompt. The 1 signifies that this is the first line of a Transact-SQL statement, and the sqlcmd prompt is the point at which the Transact-SQL statement will start when you type it in.

At the sqlcmd prompt, you may type in both Transact-SQL statements and sqlcmd commands, such as GO and EXIT. Each Transact-SQL statement is placed in a buffer called the statement cache. These statements are sent to SQL Server after you type the GO command and press ENTER. To terminate sqlcmd, type EXIT or QUIT at the start of a new line.

To clear the statement cache, type :RESET. Typing ^C causes sqlcmd to exit. ^C can also be used to stop the execution of the statement cache after a GO command has been issued.

The commands recall facilities from the Microsoft Windows command prompt may be used to recall and edit previously entered statements and commands.

Characters that are enclosed in quotes are used without any further preprocessing, with the exception that quotes can be inserted into a string by entering two consecutive quotes. SQL Server treats this character sequence as one quote. (The translation takes place in the server, however). Scripting variables will not be expanded when appearing within a string.

sqlcmd
PRINT "Length: 5"" 7'"
GO

Here is the result set.

Length: 5" 7' 

sqlcmd supports scripts with strings that span multiple lines. For example, the following SELECT statement spans multiple lines but is a single string:

SELECT First line
Second line
Third line

• If no code pages are specified, then sqlcmd will use the current code page for both input and output files, unless the input file is a Unicode file, in which case no conversion is needed.
• sqlcmd automatically recognizes both big-endian and little-endian Unicode input files. If the -u option has been specified, the output will always be little-endian Unicode.
• If no output file is specified, the output code page will be the console code page. This allows the output to be displayed correctly on the console.
• Multiple input files are assumed to be of the same code page. Unicode and non-Unicode input files can be mixed.

This is an example of what you see when running sqlcmd interactively.

When you open a command prompt window, it only has one line similar to:

C:\> _

This means the folder C:\Documents and Settings is the current folder, and if you specify a file name, Windows will look for the file in that folder.

Type sqlcmd to connect to the default instance of SQL Server on the local computer, and the contents of the command prompt window will be:

C:\>sqlcmd
1> _

This means that you have connected to the instance of SQL Server and that sqlcmd is now ready to accept Transact-SQL statements and sqlcmd commands. The flashing underscore after the 1> is the sqlcmd prompt that marks the location at which the statements and commands you type in will be displayed. Now, type USE AdventureWorks and press ENTER, and then type GO and press ENTER, and the contents of the command prompt window will be:

sqlcmd
USE AdventureWorks
GO

Here is the result set.

Changed database context to 'AdventureWorks'.
1> _

Pressing ENTER after entering USE AdventureWorks signaled sqlcmd to start a new line. Pressing ENTER after typing GO signaled sqlcmd to send the USE AdventureWorks statement to the instance of SQL Server. sqlcmd then returned a message to indicate that the USE statement completed successfully and sqlcmd displayed a new 1> prompt as a signal to you that you can enter a new statement or command.

The following example illustrates what the command prompt window contains if you type in a SELECT statement, a GO to execute the SELECT, and an EXIT to terminate sqlcmd:

sqlcmd 
USE AdventureWorks
GO
SELECT TOP (5) ContactID, FirstName, LastName
FROM Person.Contact
GO

Here is the result set.

ContactID   FirstName                                          LastName
----------- -------------------------------------------------- -----------
1           Syed                                               Abbas
2           Catherine                                          Abel
3           Kim                                                Abercrombie
4           Humberto                                           Acevedo
5           Pilar                                              Ackerman
(5 rows affected)
1> EXIT
C:\>

The lines after the line 3> GO are the output of a SELECT statement. After the output has been generated, sqlcmd resets the sqlcmd prompt and displays 1>. After entering EXIT at line 1>, the command prompt window displays the same line it did when you first opened the command prompt. This indicates that sqlcmd has terminated its session. You can now close the command prompt window by typing another EXIT command.

Running the sqlcmd Utility
Running Transact-SQL Script Files Using sqlcmd

sqlcmd Utility

Getting SQL Server 2005 Assistance