Configuring OLE DB Providers for Distributed Queries
SQL Server provides several advanced options for managing distributed queries. Some of the options are managed at the provider level and others are managed at the linked server level by using sp_serveroption. Only experienced system administrators should configure these advanced options.
OLE DB Provider Options
The OLE DB provider options for managing distributed queries can be set in SQL Server Management Studio. In Object Explorer, right-click the provider name and select Properties. The following table describes the available provider options.
Provider option |
Description |
|---|---|
DynamicParameters |
If nonzero, indicates that the provider allows for the '?' 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 enables SQL Server to execute parameterized queries against the provider. Executing parameterized queries against the provider can yield better performance for certain queries. |
NestedQueries |
If nonzero, indicates that the provider allows for nested SELECT statements in the FROM clause. Setting this option enables 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 for 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 helps protect the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing LOB columns (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) are not allowed. The SQL Server Native Client OLE DB provider cannot be instantiated out of process. An error is raised if you set the SQL Server Native Client OLE DB provider to run out of process and try to run a distributed query. |
NonTransactedUpdates |
If nonzero, SQL Server allows for 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 tries to use indexes of the provider to fetch data. By default, indexes are used only for metadata and are never opened. |
DisallowAdhocAccess |
If a nonzero value is set, SQL Server does not allow for ad hoc access through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider. When this option is not set, SQL Server also does not allow for ad hoc access. This option controls the ability of non-administrators to run ad hoc queries. Administrators are not affected by this option. |
SqlServerLike |
If nonzero, the provider supports the LIKE operator as the operator is implemented in SQL Server. When this option is set, SQL Server will consider pushing to the provider the queries that have LIKE predicates against remote columns as part of the evaluation of the distributed query. |
The OLE DB provider options operate at the provider level. When the options are set for a provider, the settings apply to all linked server definitions that are using the same OLE DB provider.
Linked Server Options
Besides the provider-level options described earlier, several options for managing distributed queries are available at the linked server level by using sp_serveroption. Unlike provider-level options, the server-level options affect only 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 uses the collation information of character columns from the linked server. If the linked server is an instance of SQL Server, the collation information is automatically derived from the SQL Server OLE DB provider interface. If the linked server is not an instance of SQL Server, SQL Server uses the collation set in the collation name option. If set to false, SQL Server interprets character data from the specified linked server in the default collation of the instance of the local SQL Server. |
collation name |
This specifies the collation that will 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, that will be used when SQL Server tries 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 check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema. If this option is set to true, schema checking 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 query compilation and execution. You may want to set this option to true when distributed partitioned views are being used against a linked server that is running SQL Server. A specified table that participates in the partitioned view may not be actually used in a specific execution of a query against the view. Therefore, deferring the schema validation may improve performance. |
See Also