How to Use T-SQL

3 4

In addition to using T-SQL in your application programs (a topic that is beyond the scope of this book), you can execute interactive T-SQL statements by using one of three SQL Server utilities—ISQL, OSQL, or Query Analyzer—or you can create and execute T-SQL scripts.

The ISQL Utility

The ISQL utility communicates with SQL Server through DB-LIB and enables you to execute interactive T-SQL statements, stored procedures, and script files. Because DB-LIB remains at the SQL Server 6.5 level of functionality, the ISQL application does not support some SQL Server 2000 features. For example, ISQL cannot retrieve data of the Unicode ntext data type.

The OSQL Utility

The OSQL utility was new in SQL Server 7 and remains as a replacement for ISQL in SQL Server 2000. These two utilities are essentially the same, except that OSQL uses Open Database Connectivity (ODBC) instead of DB-LIB to communicate with SQL Server, and OSQL supports all of the SQL Server 2000 features. The functionality of OSQL and ISQL is otherwise the same, and SQL Server 2000 supports both utilities, but you should use OSQL in place of ISQL to avoid the problem just mentioned.

To execute OSQL from a command prompt window (from an MS-DOS prompt), you simply run the OSQL.exe program with the appropriate parameters, as shown here:

  osql -U username -P password -S servername 

After OSQL makes a connection to SQL Server, the following numbered prompt appears:

  1> 

At this prompt, you can type a T-SQL statement, as shown here:

  1>    sp_helpdb master 2>    go 

This statement will cause the information about the master database to appear. The GO keyword is not a T-SQL statement; it is a command recognized by ISQL, OSQL, and Query Analyzer that signals the end of a batch of T-SQL statements. The results from an interactive query such as this are displayed in the command prompt window.

If you mistype in OSQL, you can start over at the 1> prompt by using the OSQL command RESET, as shown here:

  1>    sp_helpbd 2>    reset 1>    sp_helpdb  2>    go 

To stop the OSQL utility, type QUIT or EXIT. You can terminate a command or query while it is still running, without exiting the OSQL utility, by pressing Ctrl+C.

The -U, -P, and -S parameters are not the only parameters that the OSQL utility recognizes. For a full description of all the other parameters that the OSQL utility recognizes and for additional information about OSQL, see the topic "osql Utility" in Books Online.

The Query Analyzer

You can use the Query Analyzer to display a graphical user interface (GUI)-based tool where you can execute T-SQL statements or scripts and get the results in a formatted output. You can also perform some index and query analysis with this tool. Some people prefer using Query Analyzer to running statements in an MS-DOS prompt window. To run Query Analyzer, follow these steps:

  1. Start Query Analyzer using one of the following three methods:

    • Type isqlw at a command prompt.
    • Open Enterprise Manager and choose SQL Query Analyzer from the Tools menu.
    • From the Start menu, point to Programs, point to Microsoft SQL Server, and then choose Query Analyzer.

    The Connect To SQL Server dialog box appears (unless you are already connected to the server), as shown in Figure 13-1.

    Figure 13-1. The Connect To SQL Server dialog box.

  2. Select the name of the server you want to connect to from the SQL Server drop-down list. A period in this box stands for the local server. Type your logon information, and if you want SQL Server to start automatically if it is not currently started, select the check box next to that option. Click OK. The opening window of the Query Analyzer will appear, as shown in Figure 13-2.

    click to view at full size.

    Figure 13-2. The SQL Query Analyzer.

  3. Type any T-SQL statement or stored-procedure call in the query window, as shown in Figure 13-3. Note that the query window has now been maximized to occupy the entire Query Analyzer window.

    click to view at full size.

    Figure 13-3. A stored-procedure call in the query window of the Query Analyzer.

  4. To execute this statement, click the Execute Query button (the triangular green arrow pointing to the right) on the toolbar, or press Ctrl+E. The results will appear in the results pane, as shown in Figure 13-4.

    click to view at full size.

    Figure 13-4. The results of a query shown in the Query Analyzer.

  5. To use Query Analyzer to load and run T-SQL scripts that you created previously, click the Load SQL Script button (the yellow folder) on the toolbar or choose Open from the File menu, and then browse for the desired file. The entire script will appear in the top pane of the query window. Click the Execute Query button to run the script.

    NOTE


    Many other options are available with Query Analyzer, including some new ones for SQL Server 2000. (See Chapter 35 for more details.)

T-SQL Scripting

Creating your own scripts is a great way to run T-SQL statements or stored procedures when you might need to run them more than once. A script is simply a file that contains the T-SQL you want to execute. It can be one statement or a series of statements. One suggestion: when you are creating T-SQL scripts, give them the file extension .sql so that you can quickly identify them.

The following code shows the statements in a sample script. The script calls several system stored procedures that gather a variety of information about the MyDB database and its files, filegroups, and tables (Customer_Data and Product_Info). Let's assume that the script has been saved in the file MyDB_info.sql:

  use MyDB go sp_helpdb MyDB go sp_helpfilegroup go sp_spaceused Customer_Data go sp_spaceused Product_Info go 

To run this script via the command line, you can use the -iand -o options of OSQL. The -i option is followed by the name of the input script file to run, and the -o option is followed by the name of the output file to direct the results into—in this case, MyDB_info.out. Let's also use the -e option to have the original T-SQL statements echoed into the output file for clarity. For example, to run the preceding script as the system administrator, enter the following code at the command prompt:

  osql -U sa  -P  -i MyDB_info.sql  -o MyDB_info.out  -e 

Check the output file to verify that the script worked as you intended. Using T-SQL scripts in this way allows you to save output to a file so that you can view it later, as you wish-maybe to compare results of running the script before and after some change to the database. Also, scripting is useful when you want to run a statement or statements more than once.

Another way to run a script without entering this OSQL command every time is to create a .cmd file with the OSQL command in it. In this case, we could name the file MyDB_info.cmd, and it would contain the OSQL command we just typed. Be sure that the input and output files' filenames have directory paths specified if they are not in the same directory as the .cmd file. Now you can either run the command MYDB_INFO from a command prompt or double-click the MyDB_info.cmd filename in Microsoft Windows Explorer.

You can also run T-SQL scripts in the Query Analyzer. To run our script, MyDB_info.sql, open the file by choosing Open from the File menu in Query Analyzer and then browsing to select the file. Then click Open and the script code will appear in the top pane. Click the Execute Query button or press Ctrl+E to execute the statements. The output for each statement will appear in the order in which it was executed, as shown in Figure 13-5. Notice that the top two tables of results are both from the sp_helpdb stored procedure.

click to view at full size.

Figure 13-5. The results of a script run in the Query Analyzer.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net