Osql is a 32-bit utility run from the command line. It is used to create interactive queries and send them to SQL Server 2000. System procedures and script files are used to create queries. Osql is also used to run packages and tasks that include operating system commands on SQL Server 2000. The GO command is used to indicate the end of a packet and instructs the SQL Server service to start processing the packet. By default, the results are formatted and displayed externally in a text file. Use QUIT or EXIT to quit and return to the command line mode. The Osql utility uses the ODBC API to connect to SQL Server 2000. Osql is often used to check whether it is possible to connect to SQL Server 2000.
Note Osql replaces the utility that was included in SQL Server 6.5 and earlier versions. Isql uses the library instead of ODBC. Although the Isql utility is available as part of SQL Server 2000, it is mainly used for backward compatibility of versions. It does not support all functions provided by Osql, including working with named instances.
When you use Osql to connect to SQL Server 2000, there are many parameters in the string used to establish the connection. When entering the values of parameters passed by Osql, take into account the register. You should also know that the hyphen (-) and slash (/) replace each other. SQL Server Books Online contains a description of the syntax for all parameters supported by the Osql utility, and there are some examples of using these commands. There are two basics…
Class. Using Osql, SQL Query Analyzer and SQL Server Enterprise Manager utilities
After you have installed SQL Server 2000, reviewed the installation results and started the SQL Server service, you can start working with the basic tools and utilities of SQL Server 2000. These include the Osql utility running from the command line, as well as SQL Query Analyzer and SQL Server Enterprise Manager – the main graphical tools for creating queries and administering SQL Server 2000. As a database administrator, you will be using the tools e.g., so you should familiarize yourself with them in detail.
After studying the material of this lesson, you will be able to do so:
Use the Osql utility to connect to SQL Server 2000 and create
server administration; use SQL Query Analyzer to connect to SQL Server 2000,
create queries and administer the server; V use SQL Server Enterprise Manager to connect
to SQL Server 2000 and server administration.
The duration of the lesson is about 45 minutes
The parameter is the authentication method and the server or instance that you want to connect to.
If you want to connect to a server using Windows Authentication Tools by specifying the parameters of your Windows NT 4.0/2000 user account, then you need to specify the -E parameter on the command line of the utility. Otherwise, use the settings to send the registered SQL Server user name and password. Pay special attention to the fact that the unique SQL Server username and password are case-sensitive and are entered from the keyboard. If you want to use a server username
SQL Server, the identification mode should be selected in the SQL Server settings
Mixed Mode users. To select the server (instance) to which you want to connect, use the parameter If the name of the server or instance is not specified, Osql setting mneme with the default instance installed on the local server or with a named server. can be used to connect to local and remote servers. To display a list of all local and remote servers on the network, specify the option To connect to a named server on a local or remote server, you must specify the exact server name, as well as the name of the instance. to connect to a named instance on your local computer using Windows authentication tools, apply the command: OSQL Сервера\ Exemplar name{ris. 3.11).
To connect to a named instance you need to use the Osql SQL Server 2000 version. The Osql version, which is installed with SQL Server 7.0, can only be used to connect to the default instance.
Exercise 4: Creating and passing SQL Server 2000 queries using the Osql utility
In this exercise, you connect to SQL Server 2000 instances using the Osql utility.
* To connect to SQL Server 2000 with Osql power
- Verify that you lived on the SelfPacedSQL.MSFT domain controller
under the Administrator account.
- From the Start menu, select Run. The Run window opens.
- In the Open field, enter and press the Enter key.
The command line input window opens.
- In the command line input window, type OSQL -E and press Enter. The Osql utility connects to the default SQL Server 2000 instance on your local server and then the 1> prompt line appears on the screen, after which you can continue to enter commands. If you cannot establish a connection to SQL Server, you will receive an error message
ODBC. A common mistake, in this case, is entering lower case characters in the command line instead of upper case characters (e instead of H>*).
- In line 1 > prompt enter the command SELECT >S@SERVERNAME and press the key Enter to create and send a query to the SQL Server using the function @@SERVERNAME.
Note that the line 2> prompt will appear on the screen. The first command has not yet been executed because you have not entered the keyword for the end
of the SQL Server 2000 package.
- In line 2> prompt enter the command SELECT @VERSION and press the key Enter to create and send a query to SQL Server 2000, function @@VERSION.
Note that the line 3> prompt will appear on the screen. None of the two commands sent to the server has been executed.
- In line 3> prompt, enter the GO command and press the Enter key to send the SQL Server 2000 package for processing.
After SQL Server 2000 has received the query, it sends the name of your local server (SelfPacedCPU) and the SQL Server version (SQL Server 2000 Enterprise Evaluation Edition) installed on the client computer.
your computer. In addition, the line of received data will contain
information about the version of Windows you are using. On the command line again
I > prompt will be displayed, where you can start entering a new query.
- Enter EXIT and press the Enter key.
The Osql utility closes and the main command line of the command line utility is displayed again.
- Enter OSQL -L and press Enter.
The Osql utility displays the names of SQL Server instances installed on your local computer and available on your network.
- Enter OSQL -E -S SelfPacedCPU\MyNamedInstance and press Enter. At the same time Osql connects to the named instance of SQL Server 2000 installed on the local server and displays the line 1> prompt, where you can enter the following commands.
- Enter SELECT SYSTEM USER and press Enter. SYSTEM USER is a function used to obtain the user name currently logged in to the system,
- Enter your GO and press the Enter key.
Osql displays information about the current security context selected for SQL Server 2000. Since you use a trusted connection to connect to SQL Server 2000, the current security context is SQL\Admintstratoi The request will display the line 1> prompt, waiting for your commands.
- Enter QUIT and press Enter.
- Close the MS-DOS session window.