Microsoft Site Server ODBC Database Catalog Search

Site Server 3.0 Search is an extremely easy and powerful technology to implement. Often, when you think of search, you think of searching content in the form of Active Server Pages (ASP) and HTML on your Web site. But Site Server Search lets you index and catalog tables in databases as well. This technology is called the ODBC Database Catalog Search. With this technology, you can build search catalogs to let your site visitors search for information in your Microsoft Access or Microsoft SQL Server databases. The results of successful database searches appear on a search results page, just like a content search. From this search results page, when a site visitor clicks a link, a page appears with information from the database record. ODBC Database Catalog Search is wizard driven, and you can configure and implement it easily.

You can use either wizards or custom configuration to create Site Server Search index catalogs to search your database. Creating the ASP to perform the searches is also automatic and completely wizard driven. Behind the scenes of the ASP, a Site Server Search Catalog retrieves a reference, and ActiveX Data Objects (ADO) retrieve successful results from the search.

You can create the Search Catalog only from the Site Server Service Admin (HTML) tool in Site Server 3.0: The feature isn't available in the Site Server Service Admin (Microsoft Management Console—MMC). When you've created your Database Search, you can manage the search catalog from either the Site Server Service Admin (HTML) or the Site Server Service Admin (MMC).

Creating a Site Server 3.0 database search is simple. The Catalog Definition Wizard facilitates the entire process. When you've created a database search, pick a table from a SQL Server or Access database that you'd like to search. You need to create a System ODBC data source to your Access or SQL Server database, which you can do in Control Panel in Windows 2000 or Windows NT 4.0.

Now, access Site Server Service Admin (HTML) on your Site Server machine. Choose Search from the Service Admin home page, which will take you to the home page of Web-based administration for Site Server 3.0 Search. Choose Catalog Build Definitions from the Left Navigation window on the Search page to move to the Catalog Build Definitions page. The Catalog Build Server creates and maintains the index catalogs, which keep references to all the resources (including databases) on your Web site that you choose to be available to Site Server Search.

Click Create at the bottom of the page to start the wizard that creates a new database search catalog. Choose A Database, then click Next, and follow the wizard's prompts. When the wizard prompts you to specify whether the columns are searchable or retrievable, consider two things. If you mark a column as searchable, its value can be searched after it has been indexed. If you mark a column as retrievable, its value can be retrieved and displayed to site visitors in search scripts. To save space and improve search performance, mark as retrievable only those columns that you need to be retrievable.

Note that everybody with access to the Site Server site can execute the ASP that Site Server uses to index and display database content. If you want to control access to database content, use Internet Service Manager (ISM) to change the directory security on the directories in the /siteserver/knowledge/search/database virtual root.

Click the hyperlink on the Database Catalog Wizard Summary page to search the database table. Notice at the bottom of the screen that you can search the other fields that you've cataloged as searchable with a slightly different syntax. Choose one of the results of your successful search by clicking its hyperlink. You'll go to a page with a summary of the database record in an HTML table format. See the Site Server 3.0 Search documentation for tips about how to customize the search scripts or how to move the ASP onto your site.

Developers reading this column might think, "Why wouldn't you just do this in T-SQL or ADO?" You could, of course, and you might choose to. But the power of this type of database search is that it is

Although the ASP that Site Server Database Search Catalog Generation generates eventually uses ADO to retrieve records from the database, you avoid complex where clauses that T-SQL would have to search within table columns; the search server catalog itself handles the where clauses. The main weakness of the Site Server database search is that you're limited to searching only one table in the database. However, the Site Server online documentation covers many tricks for overcoming this drawback.