Using the Osql tool to manage SQL Server Desktop Engine (MSDE 2000) Application Introduction _ Database Related

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

What is Osql?
The Osql tool is a Microsoft Windows 32 command Prompt tool that you can use to run Transact-SQL statements and script files. The Osql tool communicates with the server using the ODBC database application programming Interface (API).

How to use Osql?
In general, you can use the osql tool in this way:
• Users interactively enter Transact-SQL statements in a manner similar to those used at the command prompt.
• Users submit osql jobs by:
1. Specify a single Transact-SQL statement to run.
2. Point the tool to a script file that contains the Transact-SQL statement that you want to run.

Interactive Input Transact-SQL statements
To display a case-sensitive list of options for the osql tool, type the following at a command prompt, and then press ENTER:
osql-?
For more information about each option for the osql tool, see the "osql Utility" topic in SQL Server online books.
To interactively enter a Transact-SQL statement, follow these steps:
1. Verify that MSDE 2000 is running.
2. Connect to MSDE 2000 (for more information, see the section titled "Connecting to the SQL Server Desktop Engine (MSDE 2000)"). At the osql command prompt, type the Transact-SQL statement, and then press ENTER. When you press the ENTER key after each line you enter, osql caches the statement on the command line. To run the currently cached statement, type go, and then press ENTER.
3. To run a batch of Transact-SQL statements, enter each Transact-SQL command on a separate line separately. Then, on the last line, type "Go" to indicate the end of the batch command and run the currently cached statement. The results of the run appear in the console window.
4. If you want to exit from osql when you press ENTER after each line you enter, type QUIT or exit and press ENTER.
Submit Osql Job
In general, you can submit osql jobs in one of two 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 for the local default instance of MSDE 2000, type a command similar to the following:
OSQL-E-q "Transact-SQL statement"
-E indicates the use of Microsoft Windows NT authentication.
-Q means running Transact-SQL statements, but does not exit osql at the end of the query.
To run Transact-SQL statements and exit osql, use the-Q argument instead of-Q.
Point the tool to a script file
To point the tool to a script file, follow these steps:
1. Create a script file (such as Myqueries.sql) that contains a batch of Transact-SQL statements.
Open a command prompt, type a command similar to the following, and then press ENTER:
Osql-e-I. Input_file
which
Input_file is the script file and its full path. For example, if your script file Myqueries.sql in the C:/queries folder, replace the parameter input_file with C:/queries/myqueries.sql.
2. The running results of the script file will appear in the console window. If you want to direct the results of a run to a file, add the-o output_file parameter to the command above. For example:
Osql-e-I. Input_file-o output_file
which
Output_file is the output file and its full path.
To eliminate the number and hint symbols in the output, add the-n option to the command above. For example:
Osql-e-I. Input_file-o output_file-n

Connecting to the SQL Server Desktop Engine (MSDE 2000)
To connect to MSDE 2000, follow these steps:
1. Verify that MSDE 2000 is running.
2. Open a command window on the computer that hosts the instance of MSDE 2000 that you want to connect to.
3. Type the following command, and then press ENTER:
Osql-e
This can connect you 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 that MSDE 2000 may not be running, or you may have provided an incorrect name for the named instance of MSDE 2000 that you installed:
[Shared Memory] SQL Server does not exist or access denied.
[Shared Memory] ConnectionOpen (Connect ()).
If you successfully connect to the server, the following prompts appear:
1>

This hint indicates that osql has started. Now you can interactively enter Transact-SQL statements, and the results will appear on the command prompt line.

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