Configuring OLE DB Providers for Distributed Queries
Microsoft® SQL Server™ provides a number of advanced options for managing distributed queries. Some of the options are managed at the provider level in the Microsoft Windows® 2000 registry, and others are managed at the linked server level through sp_serveroption. Configuring these options should be undertaken only by experienced system administrators in the interests of maximizing the performance of distributed queries against linked servers.
OLE DB Provider Options
The OLE DB provider options for managing distributed queries are set using SQL Server Enterprise Manager. In the left pane of SQL Server Enterprise Manager, right-click a linked server definition that uses the OLE DB provider for which you want to set the properties. On the General tab, click Options, and then set the properties.
| Provider option | Description |
|---|---|
| DynamicParameters | If nonzero, indicates that the provider allows '?' parameter marker syntax for parameterized queries. Set this option only if the provider supports the ICommandWithParameters interface and supports a '?' as the parameter marker. Setting this option allows SQL Server to execute parameterized queries against the provider. The ability to execute parameterized queries against the provider can result in better performance for certain queries. |
| NestedQueries | If nonzero, indicates that the provider allows nested SELECT statements in the FROM clause. Setting this option allows SQL Server to delegate certain queries to the provider that require nesting SELECT statements in the FROM clause. |
| LevelZeroOnly | If nonzero, only level 0 OLE DB interfaces are invoked against the provider. |
| AllowInProcess | If nonzero, SQL Server allows the provider to be instantiated as an in-process server. When this option is not set in the registry, the default behavior is to instantiate the provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed. |
| NonTransactedUpdates | If nonzero, SQL Server allows updates, even if ITransactionLocal is not available. If this option is enabled, updates against the provider are not recoverable, because the provider does not support transactions. |
| IndexAsAccessPath | If nonzero, SQL Server attempts to use indexes of the provider to fetch data. By default, indexes are used only for meta data and are never opened. |
| DisallowAdhocAccess | If a nonzero value is set, SQL Server does not allow ad hoc access through the OpenRowset() and OpenDataSource() functions against the OLE DB provider. When this option is not set, the default behavior is to allow OpenRowset and OpenDataSource. |
These options operate at the provider level. When the options are set for a provider, the settings apply to all linked server definitions using the same OLE DB provider.
Setting either DynamicParameters or NestedQueries to nonzero values allows SQL Server to send queries requiring this syntax to the OLE DB provider for remote query execution. These two options should be set only if the provider supports their syntax.
Linked Server Options
Several options for managing distributed queries are available at the linked server level through sp_serveroption. The server level options (in contrast to provider level options) only affect the behavior against the specified linked server.
The following table describes the various linked server options.
| Linked server options | Description |
| use remote collation | If set to true, SQL Server will use the collation information of character columns from the linked server. If the linked server is an instance of SQL Server, then the collation information is derived automatically from the SQL Server OLE-DB provider interface. If the linked server is not an instance of SQL Server, then SQL Server will use the collation set in the collation name option.
If set to false, SQL Server will interpret character data from the specified linked server in the default collation of the instance of a local SQL Server. |
| collation name | This specifies the collation to be used for character data from the linked server if use remote collation is set to true. This option is ignored if use remote collation is set to false, or if the linked server is an instance of SQL Server. |
| connection timeout | This specifies the time-out value (in seconds) to be used when SQL Server attempts to make a connection to the linked server. If this option is not set, the current value set for the global configuration option remote login timeout is used as the default. |
| lazy schema validation | If this option is set to false (the default value), SQL Server checks for schema changes that have occurred since compilation in remote tables. This occurs before query execution begins. If there is a change in the schema, SQL Server recompiles the query with the new schema.
If this option is set to true, the checking of the schema of remote tables is delayed until execution. This can cause a distributed query to fail with an error, if the schema of a remote table has changed between the time the query was compiled and executed. You may want to set this option to true when distributed, partitioned views are being used against a linked SQL Server. A given table participating in the partitioned view may not be actually used in a given execution of a query against the view, so deferring the schema validation can be useful to improve performance. |