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® registry, and others are managed at the linked server level through sp_serveroptions. Configuring these options should be undertaken only by experienced system administrators in the interests of maximizing the performance of distributed queries against linked servers.
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. This option should be set 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 would allow 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. Note that if this option is turned on, updates against the provider are not recoverable, since 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 metadata and are never opened). |
| DisallowAdhocAccess | If a nonzero value is set, SQL Server does not allow ad hoc access through the OpenRowset() function against the OLE DB provider. When this option is not set, the default behavior is to allow OpenRowset(). |
These options operate at the provider level. When the options are set for one linked server, the new 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.
All of the options can take effect on any linked server defined against the given provider.
Several options for managing distributed queries are available at the linked server level, through sp_serveroptions. Of these, the collation compatible option is the most important.
If the collation compatible option is set to true, SQL Server assumes all columns and character sets are compatible with the local server character set and collation, enabling SQL Server to send comparisons on character columns to the OLE DB provider. If the option is not set, SQL Server always evaluates comparisons on character columns locally.
You should only set the collation compatible option if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server. The character comparison semantics match that of SQL Server (and SQL-92). This option should not be set for linked servers defined against the Oracle provider, because Oracle character comparison semantics for the varchar datatype do not match that of SQL Server or SQL-92.
| Security for Linked Servers | sp_serveroption |
| sp_addlinkedserver |