Linked Server Properties (Provider Options Page)

All providers do not have the same options available. For example, some types of data have indexes available and some might not. Use this dialog box to help SQL Server understand the capabilities of the provider. SQL Server installs some common data providers, however when the product providing the data changes, the provider installed by SQL Server might not support all the newest features. The best source of information about the capabilities of the product providing the data is the documentation for that product.

Options

  • Dynamic parameter
    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.

  • Nested queries
    Indicates that the provider allows nestedSELECT 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.

  • Level zero only
    Only level 0 OLE DB interfaces are invoked against the provider.

  • Allow inprocess
    SQL Server allows the provider to be instantiated as an in-process server. When this option is not set, 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.

  • Non transacted updates
    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.

  • Index as access path
    SQL Server attempts to use indexes of the provider to fetch data. By default, indexes are used only for metadata and are never opened.

  • Disallow ad hoc access
    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, SQL Server also does not allow ad hoc access.

  • Supports 'Like' operator
    Indicates that the provider supports queries using the LIKE keyword.