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 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 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.
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:
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.
Figure 13-2. The SQL Query Analyzer.
Figure 13-3. A stored-procedure call in the query window of the Query Analyzer.
Figure 13-4. The results of a query shown in the Query Analyzer.
NOTE
Many other options are available with Query Analyzer, including some new ones for SQL Server 2000. (See Chapter 35 for more details.)
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.
Figure 13-5. The results of a script run in the Query Analyzer.