Tips for Data Access and Data Services

Optimizing Table Access

It's very easy to open a table with ADO by specifying the table name in the CommandText parameter and the adCmdTable flag in the Options parameter. However, there are now two options that do nearly the same thing with subtle variations. They are:

adCmdTable indicates that ADO should generate an SQL query to return all rows from the table named in CommandText.
adCmdTableDirect indicates that the provider should return all rows from the table named in CommandText.

The adCmdTableDirect option is new to ADO 2.0, and is specifically designed for OLE DB providers that support the IOpenRowset method to open tables directly, in addition to using generalized SQL statements. Rather than creating a Command object containing a "SELECT * FROM table" statement, the adCmdTableDirect option allows ADO to work directly with individual tables or indexes in a data store.

Appropriate times to use adCmdTableDirect include:

When the reason for opening the table is to add records, rather than to retrieve the data.
When the Recordset.Filter parameter is not set at the time the table is opened. (Filter will limit the "SELECT * FROM table" statement.)

Why Data Shaping?

Although you can achieve similar results by joining tables, the data shape reduces the amount of data returned by a query. For example, the following simple join would cause data from the parent to be duplicated for each joined row in the child table:

SELECT * FROM parent, child WHERE parent.ID = child.ID

If the parent table contains 10 rows, and each child row contains 5 rows, the resulting query would contain 50 rows. The same query using a data shape would contain only 10 rows for the parent data, with an embedded recordset containing 5 rows for each child. The arrangement is more compact, and since there is no redundant information, it is easier to manipulate.

Failures Generating the FTS Catalog

The Problem

It is possible to configure BackOffice® Server with separate service accounts for Microsoft SQL Server™ and Microsoft Exchange Server; this configuration increases system security but can prevent running applications from reading important registry keys, such as those that configure and control full-text search. The generation of a full-text search (FTS) catalog can fail if the SQL Server service account is not an administrator.

The Solution

For example, if the Microsoft SQL Server services are configured to run under a domain user account "SA," and the SA account is not a member of the local Administrators group, you could either:

1. Add the SA account to the Administrators group.

–or–

2. Using the Microsoft Windows NT® utility, REGEDT32, add read/write permissions for the following registry keys and their subkeys (if they exist):

HKEY_LOCAL_MACHINE\software\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\system\CurrentControlset\Services\Mssqlserver
HKEY_LOCAL_MACHINE\software\Microsoft\Windows NT\CurrentVersion\Perflib

Note The SA account must also have full access to the MSSQL7 directories and subdirectories.