Chapter 6: Automating Administrative Tasks
|
|
In this chapter, I'll discuss some administration stored procedures that Microsoft provides to help detect problems. Even though this chapter covers some of the common tools you use everyday, I'll be diving into a little more advanced topics surrounding those tools. I'll also discuss how to use some of the built-in system stored procedures to work around some common administrative problems.
Query Analyzer
As you probably know, Query Analyzer is the primary method of running ad hoc queries against SQL Server. It is much improved over SQL Server 7.0. Microsoft has taken the features of often-used tools like Visual Interdev and incorporated them into Query Analyzer. Some of these features include:
-
Stored procedure debugger
-
Object browser
-
Drag-and-drop queries
-
Object search
-
Enhanced methods to see execution statistics on both server and client side
-
Shortcut queries
-
Enhanced Index Tuning Wizards
In either version of SQL Server, you can execute a piece of a query by highlighting the query and pressing the F5 key (or CTRL-E). You can also leave the code unhighlighted and execute everything in the window.
| Tip | When you receive an error in Query Analyzer, you can double-click on the red error to jump to the line in your code where the error occurred. |
Command-Line Options
There are two command-line methods of executing SQL: isql and osql. All the SQL Server utilities use ODBC except isql, which uses the DB-Library API. The isql utility was kept in SQL Server for SQL Server 6.5 backward compatibility. The utility cannot use SQL Server 2000-specific features. For example, you cannot use isql to output XML. isql also truncates any columns greater than 255 characters in length and can't see columns defined with an ntext data type. With that said, for new coding, use osql instead of isql. Throughout this book, I focus on the osql utility, because it supports all the SQL Server features.
The osql utility has the parameters listed in Table 6-1. Keep in mind that these parameters are case sensitive.
| Parameter | Purpose |
|---|---|
| -? | Lists available options |
| -L | Lists locally configured servers |
| -S | Server to connect to \instance name |
| -U | Login name |
| -P | Password |
| -E | Use trusted connection |
| -H | Workstation name to appear in sp_who |
| -d | Database name to start at |
| -l | Login timeout in seconds |
| -t | Command timeout in seconds |
| -h | Number of rows between column headings |
| -s | Column separator |
| -w | Column width (default 80 characters) |
| -a | Network package size |
| -e | Input is echoed |
| -I | Turns quoted identifiers on |
| -D | DSN name to connect to |
| -c | Command terminator |
| -q | Executes query in double-quotes and stays in osql |
| -Q | Executes query in double-quotes, then exits osql |
| -n | Removes numbering from results |
| -m | Customizes error messages |
| -i | Input file for queries |
| -o | Output file for results |
| -O | Match behavior of earlier version |
| -p | Outputs performance statistics |
For example, you can execute the following command to select all the categories from the Northwind database:
osql -S(local) -Usa -P -dNorthwind -Q"SELECT * FROM categories"
| Tip | Query Analyzer also uses ODBC to query SQL Server. Even though ODBC is used, the ODBC messages are stripped from the results. To see these messages in Query Analyzer, go to the Options screen under the Tools menu. Once there, you can turn on the Parse ODBC Messages Prefixes option in the Connections tab to enable ODBC messages. |
Distributing Configuration Files
After you've configured your Query Analyzer, or you've created a company standard for connection options, you don't want to have to go to each desk and repeat the configuration manually. It's inevitable that you'd miss an option or otherwise break your standard.
You can save your configuration file and then load it on other computers. To save the file, choose Tools | Options in Query Analyzer, go to the General tab, and click Save. Configuration files are highly portable (under 100K) and have the extension .sqc. Copy the file to workstations that are using Query Analyzer, and click Load on the General tab of the Options dialog box. You're prompted to locate the file, and it's loaded for future use.
| Note | Query Analyzer also prompts you to save the file whenever you make customization changes. |
Getting Around the 256-Character Limit
One of the common newsgroup questions I see comes from people who want to know how to view columns that are over 256 characters in Query Analyzer. To do this, go to the Results tab under Tools And Options. You can then set the Maximum Characters Per Column option to increase the number of characters allowed per column.
Object Browser
The Object Browser is new in SQL Server 2000's Query Analyzer. This feature allows you to view the SQL Server objects for your database. It also gives you the ability to drag and drop object names to the Query Pane of the Query Window. It can also be used to create your core basic queries. Access the Object Browser by pressing F8. When the Object Browser appears, you can drill down to objects as granular as columns and indexes. Once you find an object, you can drag and drop it onto the Query Pane of the Query Window.
You can also right-click on an object and perform certain actions. For example, for columns, stored procedures, and indexes, you can easily create DDL to create the objects. For tables, you can script SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP statements. You can script objects to the following targets:
-
Windows clipboard
-
New .sql file
-
New window in Query Analyzer
If you create an INSERT statement with the Object Browser, the result looks similar to this:
INSERT INTO [Northwind].[dbo].[Order Details] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES(<OrderID,int,>, <ProductID,int,>, <UnitPrice,money,>, <Quantity,smallint,>, <Discount,real,>)
To replace the placeholder values with real data, highlight the query and choose Edit | Replace Template Parameters or use the CTRL-SHIFT-M shortcut.
You can also access the Object Browser options by right-clicking on any object and selecting Scripting Options. Under the Script tab, you can change what the Object Browser will script by default. For example, if you want the script to automatically include object-level permissions, you can select the Script Object-Level Permissions option. Under the Common Objects group in the Object Browser, you can view common SQL Server functions, and you can click and drag any function to the right pane.
 |
If you use the Replace Template Parameters option from the menu bar, the function doesn't place single quotes around character data. As a result, most DBAs replace the placeholder data manually.
|
Shortcut Queries
To speed up query execution, use shortcut queries, which allow you to assign shortcut key combinations to execute stored procedures or queries. To add additional shortcuts, go to the Custom tab under Tools and Customize. By default, the following three shortcut queries exist:
| Shortcut | Query |
|---|---|
| ALT-F1 | sp_help |
| CTRL-1 | sp_who |
| CTRL-2 | sp_lock |
Finding Objects
You can find almost any database object by pressing F4 while you're working in Query Analyzer. This action takes you to the Object Search tool and allows you to comb through the entire database to look for any type of object. You can also perform a search based on extended descriptions (covered in the section 'Extended Stored Procedures,' later in this chapter). After you find an object, right-click its listing to perform the same functions available in the Object Browser.
| Note | The Object Search tool will not find objects in a SQL Server 7.0 database by default. When you first go to search against the SQL Server 7.0 database, it will prompt you to install the sp_MSObjSearch stored procedure, which will enable the ability on the remote server. |
Templates
Nothing is worse than trying to remember the syntax for an obscure DBA function and having to dig through piles of documents to find it. Templates allow you to quickly generate queries from standard canned queries. To use templates, open the Object Browser in Query Analyzer and select the Templates tab at the bottom of the Object Browser. Templates are grouped by their function. For example, some predefined template groups include table creation and cursors.
Find the template you want to use and drag and drop it onto the right pane. A sample template may look like this:
-- ============================================= -- Create index basic template -- ============================================= CREATE INDEX <index_name, sysname, ind_test> ON <database_name, sysname, pubs>. <owner, sysname, dbo>.<table_or_view_name, sysname, authors> (<column_1, sysname, au_lname>, <column_2, sysname, au_fname>) GO
After you drag the template to the right pane, you need to change the placeholder values that are represented in brackets with your own values. To do this, highlight the query and choose Edit | Replace Template Parameters. The Replace Template Parameters screen (shown in Figure 6-1) allows you to quickly replace the placeholder value by changing the data in the value column, and clicking Replace All.
Figure 6-1: Replacing parameters in a template
Creating Your Own Templates
You can also create your own templates to solve common problems. You can use these custom templates to perform the following tasks:
-
Create a common company header for your stored procedures
-
Create a common access method for retrieving data
-
Create additional templates for common functions such as executing a DTS package
A common header for your stored procedures can help your company standardize its stored procedures. For example, a standardized header in template form could look like this:
-- ============================================= -- Author: <author, varchar(13), Brian Knight> -- Created : <created, datetime, 1/05/2001> -- Purpose : <purpose, varchar(40), Sample purpose> -- Company : <company, varchar(13), SQLServerCentral.com> -- =============================================
Each item in brackets represents a parameter. The value of <author, varchar(13), Brian Knight> means a character parameter named author with a default value of Brian Knight. After you create the template, save it to the templates directory. By default, the templates directory is C:\Program Files\Microsoft SQL Server\80\ Tools\Templates\SQL Query Analyzer.
You can change the location of the templates directory in the General tab under Tools And Options. If you create a directory under the Templates path, it shows up in the Templates tree when you right-click on any item in the tree and select Refresh.
|
|
