Introduction to using Osql to manage SQL Server Desktop Engine (MSDE 2000) Applications

Summary
"SQL Server Desktop Engine" (also called MSDE 2000) does not have its own user interface, because it is designed to run in the background. The user interacts with the program embedded in MSDE 2000. The only tool provided by MSDE 2000 is Osql. The executable file SQL .exe is in the MSSQL/Binn folder of the default instance of MSDE 2000.
This article focuses on how to use the Osql tool to manage MSDE 2000.

What is Osql?
Osql is a Microsoft Windows 32 command prompt tool that can be used to run Transact-SQL statements and script files. The Osql tool uses the ODBC database application programming interface (API) to communicate with the server.

How to Use Osql?
Generally, you can use the osql tool as follows:
• The user enters a Transact-SQL statement in a similar way as when using a command prompt.
• You can submit an osql job:
1. specify a single Transact-SQL statement to run.
2. Point the tool to a script file containing the Transact-SQL statement to be run.

Interactive input of Transact-SQL statements
To display the osql tool's case-sensitive Option List, type the following in a command prompt and press ENTER:
Osql -?
For more information about each option of the osql tool, see the "osql Utility" topic in "SQL Server online books.
To interactively enter a Transact-SQL statement, follow these steps:
1. Confirm that MSDE 2000 is running.
2. Connect to MSDE 2000 (for more information, see section titled "connect to SQL Server Desktop Engine (MSDE 2000 ). At the osql command prompt, type A Transact-SQL statement and press ENTER. When you press ENTER after each line, osql caches the statements on the command line. To run the current cached statement, type "Go" and press ENTER.
3. To run a batch of Transact-SQL statements, enter each of the statements on a separate line. Then, type "Go" on the last line to end the batch processing command and run the current cached statement. The running result appears in the console window.
4. When you press ENTER after each line you ENTER, to EXIT from osql, type QUIT or EXIT and press ENTER.
Submit an Osql job
Generally, you can submit an osql job in either of the following ways.
1. specify a single Transact-SQL statement.
2. Point the tool to a script file.
Specify a single Transact-SQL statement
To run Transact-SQL on the local default instance of MSDE 2000, type A Command similar to the following:
Osql-E-q "Transact-SQL statement"
-E Indicates Microsoft Windows NT authentication.
-Q indicates to run a Transact-SQL statement, but does not exit osql at the end of the query.
To run a Transact-SQL statement and exit osql, use the-Q Parameter instead of-q.
Point the tool to a script file
To direct the tool to a script file, follow these steps:
1. Create a script file (such as myQueries. SQL) containing a batch of Transact-SQL statements ).
Open a command prompt, type A Command similar to the following, and press ENTER:
Osql-E-I input_file
Where
Input_file is the script file and its complete path. For example, if the script file myQueries. SQL is in the C:/Queries folder, replace the input_file parameter with C:/Queries/myQueries. SQL.
2. The running result of the script file will appear in the console window. If you want to direct the running result to a file, add the-o output_file parameter to the preceding command. For example:
Osql-E-I input_file-o output_file
Where
Output_file is the output file and its complete path.
To remove the numbers and tooltips in the output result, add the-n option to the preceding command. For example:
Osql-E-I input_file-o output_file-n

Connect to the SQL Server Desktop Engine (MSDE 2000)
To connect to MSDE 2000, follow these steps:
1. Confirm that MSDE 2000 is running.
2. Open a command window on the computer that hosts the MSDE 2000 instance you want to connect.
3. type the following command and press ENTER:
Osql-E
You can connect to the local default instance of MSDE 2000 by using Windows authentication.
To connect to a named instance of MSDE 2000, type:
Osql-E-S servername/instancename
If you receive the following error message, it indicates that MSDE 2000 may not be running, or you may provide an incorrect name for the named instance of MSDE 2000 installed:
[Shared Memory] SQL Server does not exist or access denied.
[Shared Memory] ConnectionOpen (Connect ()).
If you successfully connect to the server, the following prompt appears:
1>

This prompt indicates that osql has been started. Now, you can enter a Transact-SQL statement interactively, and the running result will appear on the command prompt line.

Manage MSDE 2000
MSDE is applicable to all versions of Microsoft SQL Server 2000 and can be managed through the Enterprise Manager.