SQL Bible [Electronic resources] نسخه متنی

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

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

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.

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.

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.