Lesson 3: Working with Osql, SQL Query Analyzer, and SQL Server Enterprise Manager

3 4

After you have installed SQL Server 2000, reviewed the results of the installation, and started the SQL Server service, you are ready to begin working with the primary SQL Server 2000 administration tools and utilities. These are Osql, the primary command-prompt utility, and SQL Query Analyzer and SQL Server Enterprise Manager, the primary graphical tools for querying and administering SQL Server 2000. As a database administrator, you will use these tools daily and need to become very familiar with their use.

---

After this lesson, you will be able to

• Use Osql to connect to, query, and administer SQL Server 2000
• Use SQL Query Analyzer to connect to, query, and administer SQL Server 2000
• Use SQL Server Enterprise Manager to connect to and administer SQL Server 2000

Estimated lesson time: 45 minutes

---

Working with Osql

Osql is a 32-bit command-prompt utility used to query an instance of SQL Server 2000 interactively using Transact-SQL statements, system procedures, and script files. It is also used to submit batches and jobs, including operating system commands, to SQL Server 2000. Use the GO command to signal the end of a batch and tell the SQL Server service to process the batch. By default, results are formatted and returned to the console, but can also be sent to a text file. Use QUIT or EXIT to close Osql and return to a command prompt. Osql uses the ODBC API to communicate with SQL Server 2000. Osql is frequently used to test basic connectivity to SQL Server 2000.

Note

---

Osql replaces Isql, which was used by SQL Server 6.5 and earlier versions of SQL Server. Isql uses the DB-Library API rather than the ODBC API. Although Isql ships with SQL Server 2000, it is used mainly for backward compatibility and does not support all features supported by Osql, including named instances.

When using Osql to connect to SQL Server 2000, there are many arguments that you can pass as part of your connection string. Be aware that arguments passed to Osql are case-sensitive. Also, be aware that a dash (-) and a slash (/) are used interchangeably. SQL Server Books Online provides the syntax for all arguments supported by Osql, along with some examples. The two most important arguments for getting started are the authentication method and the server/instance to which you want to connect.

If you want to connect using Windows authentication using your Windows 2000 or Windows NT 4.0 user account, use the –E argument. Otherwise, use the -U and the -P arguments to pass a valid SQL Server user login ID and password. Be aware that both the login ID and the password are case-sensitive. If you want to use a SQL Server user login ID, SQL Server must be configured for Mixed Mode authentication. Use the –S argument to specify the server/instance to which you want to connect. If no server name is specified or no instance is specified, Osql connects to the default instance on the local server, or the named server. You can use Osql to connect to local and remote servers. Use the –L argument to display a list of all local instances and all remote instances broadcasting on the network. To connect to a named instance on a local or remote server, you must specify the server name followed by the instance name. For example, to connect to a named instance on your local computer using Windows authentication, use the following command: OSQL –E –S YourServerName\YourInstanceName. See Figure 3.11.

Figure 3.11

Using the Osql command with the –E switch.

Note

---

You must be using the SQL Server 2000 version of Osql to connect to a named instance. The version of Osql that shipped with SQL Server 7.0 can only be used to connect to the default instance.

Practice: Using Osql to Query SQL Server 2000 Instances

In this practice you use Osql to connect to SQL Server 2000 instances.

To use Osql to connect to SQL Server 2000 instances

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
2. Click Start and then click Run.

   The Run dialog box appears.

3. In the Open drop-down combo box, type cmd and then press Enter.

   The Command Prompt window appears.

4. Type OSQL –E and then press Enter.

   Osql connects to the default instance of SQL Server 2000 on your local server (SelfPacedCPU) and then displays a 1> prompt, waiting for more input from you. If you cannot connect, you will receive an ODBC error message. A common error at this point is typing a lowercase "e" rather than an uppercase "E".

5. At the 1> prompt, type SELECT @@SERVERNAME and press Enter to query the SQL Server 2000 instance using the @@SERVERNAME configuration function.

   Notice that the 2> prompt appears. The first command is not executed because you have not informed SQL Server 2000 of the end of a batch.

6. At the 2> prompt, type SELECT @@VERSION and then press Enter to query the SQL Server 2000 instance using the @@VERSION configuration function.

   Notice that the 3> prompt appears. Neither command is executed.

7. At the 3> prompt, type GO and then press Enter to submit the batch to SQL Server 2000 for processing.

   SQL Server 2000 is queried and returns the name of your local server (SelfPacedCPU) and the version (including the edition) of SQL Server (SQL Server 2000 - Enterprise Evaluation Edition) that is installed on your computer. In addition, the version of your Windows operating system is displayed. It also returns you to a 1> prompt for a new query.

8. Type EXIT and then press Enter.

   Osql exits and returns you to a command prompt.

9. Type OSQL –L and then press Enter.

   Osql returns the names of the instances of SQL Server installed on your local computer, or broadcasting on your network.

10. Type OSQL –E –S SelfPacedCPU\MyNamedInstance and then press Enter.

    Osql connects to the named instance of SQL Server 2000 you installed on your local server and then displays a 1> prompt, waiting for more input from you.

11. Type SELECT SYSTEM_USER and then press Enter.

    SYSTEM_USER is a niladic function used to return the current system username.

12. Type GO and then press Enter.

    Osql returns your current security context within SQL Server 2000. Because you connected to SQL Server 2000 using a trusted connection, your current security context is SelfPacedSQL\Administrator. Osql then displays a 1> prompt, waiting for more input from you.

13. Type QUIT and then press Enter.
14. Close the Command Prompt window.

Working with SQL Query Analyzer

SQL Query Analyzer is used for creating and managing database objects and testing Transact-SQL statements, batches, and scripts interactively. SQL Query Analyzer is one of the programs in the Microsoft SQL Server program group, and is available from the Start menu. When you launch SQL Query Analyzer, you can choose to connect to local or remote instances of SQL Server 2000 (as well as earlier versions of SQL Server). In the Connect To SQL Server dialog box, you can type or browse to select an instance of SQL Server to which to connect. This includes connecting to earlier versions of SQL Server. Be aware that (local) refers to the default instance on the local server and that using a period or a blank entry in the SQL Server drop-down combo box also refers to the default instance on the local server. After selecting the instance to which you want to connect, you select the authentication method you want to use to connect. You can choose either Windows authentication or SQL Server authentication. Finally, if the SQL Server instance to which you want to connect is not started, you can choose to start it.

After you connect to SQL Server using SQL Query Analyzer, you need to become familiar with the SQL Server Query Analyzer interface. See Figure 3.12.

Multiple query windows can be displayed. Each query window can be a connection to a different instance of SQL Server 2000 (or earlier version of SQL Server), or simply a different query window using the same connection. The title bar for each query window displays connection information specifying the instance, the database, and the user security context for the connection. Each query window contains a Query pane and a Results pane. You execute a query by clicking the Execute Query button on the toolbar or by pressing the F5 key or Ctrl+E. You can highlight a specific Transact-SQL statement to execute just a selected statement from a number of statements within a query window. You can also highlight a specific Transact-SQL statement or portion thereof, and then press Shift+F1 to display SQL Server Books Online for that particular statement or portion of statement. The results of a query are displayed in the Results pane. The Results pane contains multiple windows. The Grids tab displays the result set or sets from the query or queries. By default, the results are displayed as a grid, but can also be displayed as free-form text. The Message tab displays information and error messages related to the query. The Query status line also provides information regarding the query, including how long it has been running if it is still running, the number of rows returned, and the current row number if you are navigating the result set.

Figure 3.12

The SQL Query Analyzer interface.

You can configure SQL Query Analyzer to display or hide the object browser. Either press the F8 key or click the Tools menu, point to Object Browser, and click Show/Hide. The Object Browser is a powerful tool used to navigate and work with the objects in a database. The Object Browser provides object scripting, stored procedure execution, and access to table and view objects. It is used primarily by database developers, but can be very useful for database administrators as well.

Each connection has connection properties. You can view the current connection properties by clicking the Current Connection Properties button on the toolbar or by clicking Options from the Tools menu. This allows you to set the connection properties for all new connections made with SQL Query Analyzer. Do not modify these properties without fully understanding the consequences. In general, the details of the connection properties are beyond the scope of this book. Use SQL Server Books Online for more information.

Practice: Using SQL Query Analyzer to Query SQL Server 2000 Instances

In this practice you use SQL Query Analyzer to query SQL Server 2000 instances.

To use SQL Query Analyzer to query SQL Server 2000 instances

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

   SQL Query Analyzer appears displaying the Connect To SQL Server dialog box.

3. Verify that Windows Authentication is selected and then click OK.

   You are connected to the default instance of SQL Server 2000 on your computer using your Windows user account. Verify this by reviewing the active query window title bar.

4. Press the F8 key to toggle the Object Browser. Leave the Object Browser visible.
5. In the Query pane, type SELECT * FROM INFORMATION_SCHEMA.SCHEMATA.

   This query will use information schema views to query this instance of SQL Server 2000 for all databases in this instance. Information schema views will be covered in Chapter 5. Notice the color coding. Blue indicates a keyword and gray indicates an operator. Refer to SQL Server Books Online for more information regarding color coding.

6. On the toolbar, click the Execute Query button.

   Notice that the result set from the query is displayed in the Results pane in the form of a grid. Information regarding all six databases is returned.

7. In the Object Browser, expand Master and then expand Views.
8. Right-click INFORMATION_SCHEMA.SCHEMATA and then click Open.

   Notice that the Open Table window displays the same information as the previous query.

9. Close the Open Table window.
10. In the Results pane of the original query, click the Messages tab.

    An informational message regarding the number of rows affected by the query (6 rows affected) is displayed.

11. In the Query pane, select INFORMATION_SCHEMA.SCHEMATA and then press Shift+F1.

    SQL Server Books Online appears displaying information regarding INFORMATION_SCHEMA.SCHEMATA in the console tree.

12. In the SQL Server Books Online console tree, double-click INFORMATION_SCHEMA.SCHEMATA view and then review the information in the details pane for Schemata.
13. Close SQL Server Books Online.
14. In the Query pane of SQL Query Analyzer, type SELECT @@SERVERNAME on a new line.

    Notice that the color of @@SERVERNAME changed to magenta when SQL Query Analyzer recognized this character string.

15. Select this new query only, and then press Ctrl+E to execute just this query.

    Notice that you can select and execute a single query in a query window. The name of your server (SelfPacedCPU) is returned.

16. On the toolbar, click the Clear Window button.

    The contents of the Query pane are erased.

17. On the toolbar, click the Show Results Pane button.

    This toggles the Results pane, hiding the Results pane.

18. Press Ctrl+R.

    This toggles the Results pane again, restoring the Results pane to visibility.

19. On the toolbar, click the New Query button.

    A new query window appears. Compare the two Query panes. Notice that you are connected to the same database in the same instance of SQL Server 2000 using the same security context.

20. In the active query window, type USE Northwind and then execute the query.

    Notice that the current database displayed on the toolbar changed to Northwind. Also notice that the active query window title bar now indicates a connection to the Northwind database rather than the Master database.

21. On the File menu, click Connect.

    The Connect To SQL Server dialog box appears.

22. Next to the SQL Server drop-down combo box, click the ellipsis ( . . . ) and select SelfPacedSQL\MyNamedInstance and then click OK.

    Notice that this named instance now appears in the SQL Server drop-down combo box.

23. Click OK to connect to your named instance.

    A new query window appears. The title bar indicates that you are connected to the Master database in your named instance. Notice that the current database on the toolbar is Master.

24. Close SQL Query Analyzer.
25. A SQL Query Analyzer message box appears.
26. Click the No To All button. Do not save any queries.

Working with SQL Server Enterprise Manager

SQL Server Enterprise Manager is the primary tool for server and database administration. SQL Server Enterprise Manager is one of the programs in the Microsoft SQL Server program group, and is available from the Start menu. When you launch SQL Server Enterprise Manager from the Start menu, a preconfigured Microsoft Management Console (MMC) console appears in user mode containing a snap-in for the administration of SQL Server 2000. If you open this MMC console in author mode, you can add additional snap-ins to this console (such as a snap-in for Event Viewer) to facilitate performing multiple common administration tasks using a single MMC console. To open the SQL Server Enterprise Manager MMC console in author mode, right-click the SQL Server Enterprise Manager.msc file in the \Program Files\Microsoft SQL Server\80\Tools\Binn folder and click Author. For more information regarding MMC consoles, use Windows 2000 Books Online.

After you open SQL Server Enterprise Manager, you need to become familiar with the interface. See Figure 3.13.

Figure 3.13

The SQL Server Enterprise Manager interface.

The left pane of an MMC console is the console root container that contains separate console trees for each snap-in. A console tree is a hierarchical structure containing folders, extension snap-ins, monitor controls, tasks, wizards, and documentation. The SQL Server Enterprise Manager console contains the Microsoft SQL Servers console tree in the left pane. The Microsoft SQL Servers console tree contains the SQL Server Group container. This is the default group (or container) for all SQL Servers registered in this console for administration. When you install an instance of SQL Server 2000, the Setup program automatically registers that instance for administration on the local computer and places it in this default group. Each SQL Server 2000 instance has its own container. You can create separate groups containing selected servers for administrative convenience when administering many servers.

Note

---

Registering additional SQL Server 2000 instances is covered in Chapter 12.

When you click an object in a console tree, the right pane of the MMC console (called the details pane) displays additional subcontainers or the contents of that object, depending upon the object. You can specify and customize the view of the details pane, including modifying the items that appear on the toolbar when the object is selected. Some objects in the console tree have preconfigured taskpad views for that object. These views include preconfigured report information and shortcuts to relevant wizards to make it easier for you to perform certain tasks. Taskpad views are HTML pages. By default, the taskpad views are not enabled.

An MMC console contains two types of toolbars. The first is the MMC toolbar. This is primarily used when you are in author mode. The second type of toolbar is specific to each console tree. If your focus is within the Microsoft SQL Servers console tree, the console root toolbar (directly beneath the MMC toolbar) will contain menu items and tools specific to SQL Server Enterprise Manager. If your focus is within another console tree within the MMC console (such as Event Viewer), the console root toolbar will be specific to that console tree. The console root toolbar for SQL Server Enterprise Manager contains three context-sensitive menus: Action, View, and Tools. These menu items allow you to perform a variety of tasks, including launching other SQL Server 2000 tools, such as SQL Query Analyzer and SQL Profiler. The items that are available from each menu vary depending upon your focus within the console tree. For example, most items on the Tools menu are unavailable until your focus is on a specific instance of SQL Server 2000, because these tools act upon a particular instance.

To establish a connection to an instance of SQL Server 2000 that is registered in SQL Server Enterprise Manager, simply expand the container for that instance. You can also right-click an instance of SQL Server 2000 to start, stop, pause, or disconnect from that instance. By default, SQL Server Enterprise Manager will connect using Windows authentication and will start SQL Server 2000 if it is not already started. You can change these registration configuration defaults by right-clicking the container for an instance of SQL Server 2000 and editing the properties of the registration. For example, you can choose to connect to SQL Server 2000 using SQL Server authentication using the sa account. You can also choose to hide all system databases and objects as part of the registration configuration.

Finally, be aware that SQL Server Enterprise Manager connects to an instance of SQL Server 2000 as a client. By default, the SQL Server Enterprise Manager client polls the SQL Server service every 10 seconds to verify its state. In addition, changes to objects displayed in SQL Server Enterprise Manager after you establish a connection to a SQL Server 2000 instance generally will not be reflected immediately. This can include changes made by SQL Server Enterprise Manager and by SQL Query Analyzer. To update a particular object and its contents, right-click the object and click Refresh. To refresh all objects in a SQL Server instance, disconnect from the instance and then reconnect.

Practice: Working with the SQL Server Enterprise Manager MMC Console

In this practice you work with the SQL Server Enterprise Manager MMC console.

To work with the SQL Server Enterprise Manager MMC console

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

   SQL Server Enterprise Manager appears displaying the Microsoft SQL Servers console tree in the console root. No other console trees appear.

3. On the MMC toolbar, click Console.

   Notice the only option available is Exit. No other menu options are available because SQL Server Enterprise Manager was opened in user mode.

4. Click Exit to close SQL Server Enterprise Manager.
5. Click Start, point to Search, and then click For Files Or Folders.

   The Search Results dialog box appears.

6. In the Search For File Or Folders Named: text box, type *.msc and then click the Search Now button.

   Notice that a plethora of preconfigured MMC consoles appear. Most are separate MMC consoles each for a specific Windows 2000 administrative task.

7. Right-click SQL Server Enterprise Manager.msc and then click Author.

   The SQL Server Enterprise Manager MMC console appears in author mode.

8. On the MMC toolbar, click Console and then click Add/Remove Snap-in.

   The Add/Remove Snap-in dialog box appears.

9. Click the Add button.

   The Add Standalone Snap-in dialog box appears displaying all of the available standalone snap-ins that may be added.

10. Select Event Viewer and then click the Add button.

    The Select Computer dialog box appears.

11. Click the Finish button to accept the default configuration, which is to always manage the local computer.
12. Click the Close button to close the Add Standalone Snap-in dialog box and then click OK to close the Add/Remove Snap-in dialog box.

    The SQL Server Enterprise Manager MMC console now displays two separate console trees, Microsoft SQL Servers and Event Viewer (Local).

13. On the Console menu, click Exit.

    A Microsoft Management Console message box appears.

14. Click the Yes button to save these new console settings to the default SQL Server Enterprise Manager MMC console.
15. Close the Search Results dialog box.
16. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

    SQL Server Enterprise Manager appears displaying the Microsoft SQL Servers and the Event Viewer (Local) console trees in the console root.

17. Click the Event Viewer (Local) console tree container.

    The logs available within Event Viewer appear in the details pane. Notice that the menu items and tools on the console root toolbar change when you change console trees. The console root title bar indicates your focus within the console root.

18. Click the Microsoft SQL Servers console tree container.

    Notice that the menu items and tools on the console root toolbar specific to SQL Server Enterprise Manager appear in place of the items on the Event Viewer toolbar.

19. Expand the Microsoft SQL Servers container and then expand the SQL Server Group container.

    The default instance (SelfPacedCPU) and your named instance (MyNamedInstance) appear in the Microsoft SQL Servers console tree, each in their own container and displaying the state of the SQL Server service for that instance. Each instance also displays the authentication method used to connect to the instance, enclosed in parenthesis (namely Windows authentication).

    Note

    ---

    If the named instance is not registered, right-click SQL Server Group and then click New SQL Server Registration. Follow the instructions in the wizard to complete the registration.

    Notice that your focus in the console tree remains the Microsoft SQL Servers container and that the contents of the details pane do not change when you expand an item in the console tree. The details pane changes only when your focus changes.

20. Click the container for your default instance.

    Notice that the details pane displays the contents of this container. We will cover each of these objects in detail throughout the course of this book. Also notice that the icon indicating the state of the SQL Server service changed from a green triangle in a white circle to a white triangle in a green circle. This indicates that you have established a connection to this instance.

21. On the SQL Server Enterprise Manager toolbar, click the View menu.

    Notice the available options, including Taskpad. The container object that is your current focus contains a preconfigured view.

22. Click Taskpad.

    Notice that the details pane changes to display the taskpad view for this container object. The taskpad contains two tabs, General and Wizards. The General tab displays information regarding your computer and your server configuration. The Wizards tab displays the wizards that are available for your use. These wizards are also available from the Tools menu. We will use these wizards in exercises in later chapters of this book.

23. In the Microsoft SQL Servers console tree, right-click the container for your default instance and then click Edit SQL Server Registration Properties.

    Notice the configured registration properties.

24. Click Cancel.
25. Close SQL Server Enterprise Manager.

Lesson Summary

SQL Server 2000 contains a number of client tools and utilities to administer SQL Server 2000. Osql is the primary command-prompt utility used for the submission of batches of Transact-SQL statements to SQL Server 2000. SQL Query Analyzer is the primary graphical tool used for interactive testing of Transact-SQL statements and batches. It is also used to create and administer objects within SQL Server 2000. SQL Server Enterprise Manager is the primary graphical tool used to graphically administer objects in SQL Server 2000. SQL Server Enterprise Manager is a preconfigured MMC console that you might want to customize. Becoming familiar with each of these tools is an essential task for a database administrator because he or she will use these tools daily.