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
summary
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. Users interact with MSDE 2000 through programs embedded in it. 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 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 uses the ODBC database application programming interface (API) to communicate with the server.
How to use Osql?
Generally, you can use the osql tool like this:
•Users interactively enter Transact-SQL statements in a manner similar to when using 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 statements to be run.
Interactively enter Transact-SQL statements
To display the case-sensitive list of options for the osql tool, type the following at the command prompt, and then press ENTER:
osql-?
For more information about each option of the osql utility, see the osql Utility topic in SQL Server Books Online.
To enter Transact-SQL statements interactively, follow these steps:
1. Confirm that MSDE 2000 is running.
2. Connect to MSDE 2000 (for more information, see the section titled Connecting to SQL Server Desktop Engine (MSDE 2000)). At the osql command prompt, type the Transact-SQL statement, and then press ENTER. When you press ENTER after each line you enter, osql caches the statements on that command line. To run the currently cached statement, type Go and press ENTER.
3. To run a batch of Transact-SQL statements, enter each Transact-SQL command on a separate line. Then, type Go on the last line to signal the end of the batch command and run the currently cached statement. The running results appear in the console window.
4. When you press the ENTER key after each line you enter, if you want to exit from osql, type QUIT or EXIT and press the ENTER key.
Submit Osql job
Generally, you can submit an osql job 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 against the local default instance of MSDE 2000, type a command similar to the following one:
osql -E -q Transact-SQL statement
-E means use Microsoft Windows NT authentication.
-q means to run the Transact-SQL statement but 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 point 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 then press ENTER:
osql -E -i input_file
in
input_file is the script file and its full path. For example, if the script file myQueries.sql is in the C:/Queries folder, replace the parameter input_file with C:/Queries/myQueries.sql.
2. The results of running the script file will appear in the console window. If you want to direct the results of the run to a file, add the -o output_file parameter to the above command. For example:
osql -E -i input_file -o output_file
in
output_file is the output file and its full path.
To eliminate numbers and prompts from the output, add the -n option to the above command. For example:
osql -E -i input_file -o output_file -n
Connect to 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 hosting the instance of MSDE 2000 to which you want to connect.
3. Type the following command and press ENTER:
osql -E
This is done by using Windows Authentication to connect you to the local default instance of MSDE 2000.
To connect to a named instance of MSDE 2000, type:
osql -E -S servername/instancename
If you receive the following error message, MSDE 2000 may not be running, or you may have given the wrong name to the named instance of MSDE 2000 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 prompt will appear:
1>
This prompt indicates that osql has been started. You can now enter Transact-SQL statements interactively and the results will appear at the command prompt.
Managing MSDE 2000
MSDE is fully applicable to all versions of Microsoft SQL Server 2000 and can be managed through Enterprise Manager.