Using Microsoft SQL Server Utilities to Access Database

Microsoft SQL Server 2000 provides two command-line utilities — ISQL and OSQL — to access databases. In addition, Microsoft SQL Server 2000 includes Microsoft Query Analyzer — a graphical user interface to execute ad hoc queries and scripts and analyze output.

Using Microsoft SQL Server command line interface

ISQL is a legacy application maintained for backward compatibility. It uses DBLIB (a native call level interface [CLI] for MS SQL Server) and does not support all new features of MS SQL Server 2000. When using it, a user is limited to MS SQL Server 6.5 capabilities only — for example, it has no support for Unicode scripts. The only advantage of using it is that it might offer a slightly faster performance in comparison to ODBC-based OSQL.

OSQL was introduced for the first time with MS SQL Server 7.0. It uses an Open Database Connectivity (ODBC) API to connect to the RDBMS (see Chapter 16 for more information on SQL API), and it uses default ODBC settings as defined in the SQL2 standard. It has full support for all the new features of MS SQL Server 2000 and is recommended for use whenever there is no need to be bound by the restrictions of earlier versions of SQL Server. Here we are giving only the basics to help you feel comfortable while trying on examples from this book; for"the full list of available options, refer to MS SQL Server 2000 documentation. (Books Online are included with its installation.)

To start OSQL from an MS-DOS prompt, type the following command:

C:\> osql /U sa /P   <pasword>

This command opens connection to the default database (usually Master) for user (/U) sa identified by password (/P). If you installed MS SQL Server 2000 with all defaults as described in Appendix D, then your user ID will be sa, and your password will be whatever you've specified during the installation, and they would not be case sensitive.

To connect to a specific database, you would need to supply an additional parameter — the database name. For example

C:\> osql /d acme /U acme /P   acme

This example assumes existence of database acme in your MS SQL Server installation, as well as that of user acme with password acme.

If you missed one of the required parameters (like /P), you will be prompted for it. If none of the parameters are specified, OSQL checks environmental variables on your computer OSQLUSER=(user) and OSQLSERVER=(server); if these are undefined, then the workstation name is used; it also will attempt to use Windows Authentication Mode to log onto SQL Server; thus, it will use your Windows account under which the OSQL utility is running.

Once a connection is established, your screen would look like that on Figure E-4. Now you can type in your SQL statements as they are given in this book, or you could load a script file — that is, a file that contains prepared SQL statements.

Figure E-4: OSQL session window

To run the SQL statement — or multiple statements — (i.e., to submit them to MS SQL Server for execution) you'd need to type GO at the end of the last statement, and press Enter; if you enter the scripts via file then the GO is assumed and the statements in the file are executed immediately.

To run scripts stored in a file, you must specify the file name as well as a fully qualified path to that file if it is located in the directory other than the current one. Assuming that the file is in the current directory, the syntax might look like this:

C:\>osql /d acme /U acme /P   acme /i create_tables.sql

Note 

The GO statement to actually run the query is a default requirement for MS SQL Server; it is possible to specify a different command terminator. For example, if you wish to have a semicolon in place of the GO (syntax a la Oracle), then add one more parameter when starting OSQL:

C:\>osql /d ACME /U acme  /P acme /c ; 

GO, however, serves more than one purpose. For example, if you need to run the same SQL statement 10 times, then you can type GO 10 as the last statement of your script or query.

The results of the executed query are printed at the end of execution (or an error message is displayed). There is a limit of 1,000 characters per line, and results that exceed this limit would be split across multiple lines.

The output could be redirected to a file instead of standard screen. You need to specify that along with the name of the file (which will be created in the current directory unless some other directory is specified):

C:\>osql   /d acme /U acme /P acme /i create_tables.sql /o output.log

In addition to SQL statements (rather Transact-SQL statements) you could use the OSQL commands in Table E-4 to control the utility behavior.

Table E-4: Basic OSQL Commands

Command	Description
!! (double exclamation mark)	Executes MS-DOS command.
Ctrl-C (key combination)	Ends query without exiting to MS-DOS.
ED	Invokes default built-in editor.
EXIT	Exits the OSQL utility into MS-DOS.
GO	Executes cached SQL statements.
QUIT	Same as EXIT.
RESET	Clears all statements that are currently displayed in the window.

Note 

The default editor is EDIT — a rather old cumbersome MS-DOS editor program. If you intend using it make sure you know how to control it with your keyboard; press Alt to access the menu bar and then use the arrow keys to navigate it. To change the default editor to, say, Notepad, you would need to execute SET EDITOR = notepad command from the MS-DOS command prompt. To edit an SQL query type ED (or EDIT) while connected to the server; your editor would be brought up, and you may type in your query or edit the one you've just entered at the SQL prompt. Once you've closed the editor (do not forget to save your query first!) the edited text will appear at the SQL prompt; type GO and press Enter to execute it.

To get full list of OSQL commands, just type osql /? at the MS-DOS prompt.

Using Microsoft Query Analyzer to execute queries

Windows is a graphical environment and MS SQL Server comes with a full-featured GUI utility — MS Query Analyzer. You can invoke this utility from MS SQL Server Enterprise Manager Console, from the StartàProgramsàMicrosoft SQL ServeràQuery Analyzer menu option, or from the command line by its name ISQLW. It is the most commonly used query interface to Microsoft SQL Server.

Note 

While it is possible to run ISQLW without the user interface by specifying connect information and output/input files, there is no reason for doing this if you use it interactively.

From the MS-DOS command line type isqlw. For example:

C:\>isqlw

This will bring up the dialog box shown in Figure E-5.

Figure E-5: MS SQL Server Query Analyzer dialog box

If you set up your SQL Server to use Windows Authentication, press the OK button — you will be logged in with your Windows login/password credentials; otherwise select SQL Server Authentication and supply User ID and Password to connect.

Once connected, you can type in and execute any query, load script file, and so on. Of course, Query Analyzer provides a much richer set of tools than a humble command-line interface, but the use of these tools would be a topic for a Microsoft SQL Server book. All we need to know for the purpose of running and executing our sample queries is how to load and execute them, and — hopefully — to see the results.

An SQL query is always executed in the context of some database. If your query brings back an error, complaining about Invalid Object, chances are that you executed the query against wrong database. Make sure that you have selected ACME as the database context in the drop-down list (it has Master by default). To run interactive queries you may just type SQL statement in the query pane; there is no need to add the GO statement at the end — press F5 (or click on the green triangle on the toolbar). The results of the query (or an error message — in case the query was unsuccessful) will be displayed at the lower part of the split pane (see Figure E-6).

Figure E-6: Executing SQL commands using Query Analyzer

Loading and running SQL script file is very intuitive. Click the Load Script button on the toolbar of the Query Analyzer (or press Ctrl-Shift-P); the standard dialog box appears to allow you to select a file very much in the same way as you would open any other file in Windows OS. Once the SQL statements from the file are loaded into the query pane, you may run them just as any other query — by pressing F5 key on the keyboard or Run button (the triangle to the right of the checkmark) on the toolbar.