Running Transact-SQL Statements Interactively Using osql
Topic last updated -- January 2004
You can use the osql utility interactively to execute Transact-SQL statements in a command prompt window. To interactively execute Transact-SQL statements using osql, run the utility without using the -Q, -q, or -i switches to specify any input files or queries. For example:
osql -E -S ComputerName\InstanceNameWhen it is executed without input files or queries, osql connects to the specified instance of SQL Server and then displays a new line with a 1> followed by a blinking underscore, called the osql prompt. The 1 signifies that this is the first line of a Transact-SQL statement, and the osql prompt is the point at which the Transact-SQL statement will start when you type it in.
At the osql prompt, you may type in both Transact-SQL statements and osql commands, such as GO and EXIT. As you type Transact-SQL statements and press ENTER, osql caches the statements but does not run them. To run the cached Transact-SQL statements, type GO at the start of a new line, and then press ENTER. osql then sends to the instance of SQL Server all Transact-SQL statements it has cached . All of the Transact-SQL statements that are entered from one GO to the next GO are called a batch of Transact-SQL statements. After you have run the last batch of Transact-SQL statements, type EXIT or QUIT at the start of a new line to terminate osql.
Interactive osql Example
This is an example of what you see when running osql interactively.
When you open a command prompt window, it only has one line similar to:
C:\Documents and Settings> _
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.
If you then type in osql -E to connect to the default instance of SQL Server on the local computer, the contents of the command prompt window will be:
C:\Documents and Settings>osql -E
1> _
This means that you have connected to the instance of SQL Server and that osql is now ready to accept Transact-SQL statements and osql commands. The flashing underscore after the 1> is the osql prompt that marks location at which the statements and commands you type in will be displayed. If you then type USE Northwind and press ENTER, and then type GO and press ENTER, the contents of the command prompt window will be:
C:\Documents and Settings>osql -E
1> USE Northwind
2> GO
1> _
Pressing ENTER after entering USE Northwind signaled osql to start a new line. Pressing ENTER after typing GO signaled osql to send the USE Northwind statement to the instance of SQL Server. A USE statement has no output, so when SQL Server sent osql a message that the USE statement completed successfully, osql displayed a new 1> _ prompt as a signal to you that you can enter a new statement or command.
This illustrates what the command prompt window contains if you now type in a SELECT statement, a GO to execute the SELECT, and an EXIT to terminate osql:
C:\Documents and Settings>osql -E
1> USE Northwind
2> GO
1> SELECT SupplierID, CompanyName
2> FROM Suppliers
3> WHERE CompanyName LIKE 'T%'
4> GO
SupplierID CompanyName
----------- ----------------------------------------
4 Tokyo Traders
(1 row affected)
1> EXIT
C:\Documents and Settings>
The lines after the line 4> GO show how osql displays the output of a SELECT statement. After the EXIT command, the command prompt window displays the same line it did when you first opened the command prompt. This indicates that osql has terminated. You can now close the command prompt window by typing another EXIT command.