These are the high-level steps that SQL Server performs when it connects to an OLE DB provider:
SQL Server creates a data source object.
SQL Server uses the provider’s ProgID to instantiate its data source object (DSO). The ProgID is specified as the provider_name parameter of a linked server configuration or as the first argument of the OPENROWSET function in the case of an ad hoc name.
SQL Server instantiates the provider’s DSO through the OLE DB service component interface IDataInitialize. This allows the Service Component Manager to aggregate its services, such as scrollability and update support, above the native functionality of the provider. Further, instantiating the provider through IDataInitialize allows the OLE DB service component to pool connections to the provider, thereby reducing some of the connection and initialization overhead.
A given provider can be configured to be instantiated either in the same process as SQL Server or in its own process. Instantiating in a separate process protects the SQL Server process from failures in the provider. At the same time, there is a performance overhead associated with marshalling OLE DB calls out-of-process from SQL Server. A provider can be configured to be instantiated in-process or out-of-process by setting the Allow In Process provider option. For information about setting provider options, see SQL Server Books Online.
To take advantage of the OLE DB service components and session pooling, see the OLE DB documentation for provider requirements.
The data source is initialized.
After the DSO has been created, the IDBProperties interface sets the DBPROP_INIT_TIMEOUT initialization property if the server configuration option remote login timeout is greater than 0; this is a required property.
These properties are set if they are specified or implied in either the linked server definition or in the second argument of the OPENROWSET function:
After these properties are set, IDBInitialize::Initialize is called to initialize the DSO with the specified properties.
SQL Server gathers provider-specific information.
SQL Server gathers several provider properties to be used in distributed query evaluation; these properties are retrieved by calling IDBProperties::GetProperties. All of these properties are optional; however, supporting all relevant properties allows SQL Server to take full advantage of the provider’s capabilities. For instance, DBPROP_SQLSUPPORT is needed to determine whether SQL Server can send queries to the provider. If this property is not supported, SQL Server will not use the remote provider as a SQL command provider even if it is one. In the following table, the Default value column indicates what value SQL Server assumes if the provider does not support the property.
| Property | Default value | Use |
|---|---|---|
| DBPROP_DBMSNAME | None | Used for error messages. |
| DBPROP_DBMSVER | None | Used for error messages. |
| DBPROP_PROVIDERNAME | None | Used for error messages. |
| DBPROP_PROVIDEROLEDBVER | 1.5 | Used to determine availability of 2.0 features. |
| DBPROP_CONCATNULLBEHAVIOR | None | Used to determine whether the NULL concatenation behavior of provider is the same as SQL Server. |
| DBPROP_NULLCOLLATION | None | Allows sorting/index-use only if NULLCOLLATION matches SQL Server’s null collation behavior. |
| DBPROP_OLEOBJECTS | None | Determines whether provider supports structured storage interfaces for large data object columns. |
| DBPROP_STRUCTUREDSTORAGE | None | Determines which of the structured storage interfaces are supported for large object types (among ILockBytes, Istream, and ISequentialStream). |
| DBPROP_MULTIPLESTORAGEOBJECTS | False | Determines whether more than one large object column can be open at the same time. |
| DBPROP_SQLSUPPORT | None | Determines whether SQL queries can be sent to the provider. |
| DBPROP_CATALOGLOCATION | DBDROPVAL CL_START |
Used to construct multipart table names. |
| SQLPROP_DYNAMICSQL | False | SQL Server-specific property: if it returns VARIANT_TRUE, it indicates that ‘?’ parameter markers are supported for parameterized query execution. |
| SQLPROP_NESTEDQUERIES | False | SQL Server specific property: if it returns VARIANT_TRUE, it indicates that the provider supports nested SELECT statements in the FROM clause. |
The following three literals are retrieved from IDBInfo::GetLiteralInfo: DBLITERAL_CATALOG_SEPARATOR, DBLITERAL_SCHEMA_SEPARATOR (to construct a full object name given its catalog, schema, and object name parts), and DBLITERAL_QUOTE (to quote identifier names in a SQL query sent to the provider).
If the provider does not support the separator literals, SQL Server uses a period (.) as the default separator character. If the provider supports only the catalog separator character but not the schema separator character, SQL Server uses the catalog separator character as the schema separator character also. If the provider does not support DBLITERAL_QUOTE, SQL Server uses a single quotation mark (‘) as the quoting character.
Note If the provider’s name separator literals do not match these default values, the provider must expose them through IDBInfo for SQL Server to access its tables through four-part names. If these literals are not exposed, only pass-through queries can be used against such a provider.
For information about exposing the SQLPROP_DYNAMICSQL and SQLPROP_NESTEDQUERIES properties, see “SQL Server-Specific Properties” later in this chapter.