SQL Server and OLE DB
OLE DB is an API that allows COM applications to consume data from OLE DB data sources. OLE DB data sources include data stored in many different formats, not only SQL databases. An application uses an OLE DB provider to access an OLE DB data source. An OLE DB provider is a COM component that accepts calls to the OLE DB API and does whatever is necessary to process that request against the data source.
Microsoft® SQL Server™ 2000 includes a native Microsoft OLE DB Provider for SQL Server used by OLE DB applications to access the data in SQL Server. The OLE DB Provider for SQL Server complies with the OLE DB 2.0 specification. Each OLE DB provider supports a command language; the OLE DB Provider for SQL Server accepts the command syntax specified as DBGUID_SQL. DBGUID_SQL syntax is primarily SQL-92 syntax with ODBC escape sequences.
In SQL Server version 6.5 and earlier, OLE DB applications had to use the OLE DB Provider for ODBC layered over the Microsoft SQL Server ODBC driver. While OLE DB applications can still use the OLE DB Provider for ODBC with the SQL Server ODBC driver, it is more efficient to use only the OLE DB Provider for SQL Server.
OLE DB is the API recommended for tools, utilities, or system level development needing either top performance or access to SQL Server features not exposed through ADO. The core capabilities of the OLE DB specification provide all the data access functionality needed by most applications. In addition, OLE DB allows individual providers to define provider-specific mechanisms to support additional features of the data engine accessed by the provider. ADO applications cannot access some SQL Server features exposed through provider-specific features of the OLE DB Provider for SQL Server, so applications needing to use the provider-specific features of the OLE DB Provider for SQL Server must use the OLE DB API. These features include:
- An IRowsetFastLoad interface to the SQL Server bulk copy component.
- An ISQLServerErrorInfo interface to get SQL Server-specific information from messages and errors.
- A LINKEDSERVERS rowset that exposes catalog information from the linked servers used in SQL Server distributed queries.
- Various provider-specific properties to control SQL Server-specific behaviors.
OLE DB also supports the XML functionality of SQL Server 2000. This provides an easy migration path for Internet applications coded to use OLE DB to retrieve a rowset and then convert it into an XML document. The application can instead use OLE DB to execute an XPath query or a SELECT statement with a FOR XML clause, in which case the result set is built as an XML document on the server rather than having to be converted on the application computer. Most application working with XML are written in ADO or use URLs, which are less complex than OLE DB.