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.