Oledb Driver For Sql Server
To enable ADO to use new features of recent versions of SQL Server, some enhancements have been made to the OLE DB Driver for SQL Server which extends the core features of OLE DB. These enhancements allow ADO applications to use newer SQL Server features and to consume two data types introduced in SQL Server 2005 (9.x): xml and udt.
The OLE DB Driver for SQL Server is a stand-alone data access application programming interface (API), used for OLE DB, that was introduced in SQL Server 2005 (9.x). OLE DB Driver for SQL Server delivers the SQL OLE DB driver in one dynamic-link library (DLL). It also provides new functionality above and beyond that supplied by the Windows Data Access Components (Windows DAC, formerly Microsoft Data Access Components, or MDAC). The OLE DB Driver for SQL Server can be used to create new applications or enhance existing applications that need to take advantage of features introduced in SQL Server 2005 (9.x), such as multiple active result sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.
Note
For a list of the differences between OLE DB Driver for SQL Server and Windows DAC, plus information about issues to consider before updating a Windows DAC application to OLE DB Driver for SQL Server, see Updating an Application to OLE DB Driver for SQL Server from MDAC.
Microsoft OLE DB Driver 18 for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to Microsoft SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, Analytics Platform System, Azure SQL Database and Azure SQL Data Warehouse. Jun 14, 2018 Microsoft OLE DB Driver for SQL Server.; 4 minutes to read +3; In this article. APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse Download OLE DB Driver. The OLE DB Driver for SQL Server is a stand-alone data access application programming interface (API), used for OLE DB, that was introduced in SQL Server 2005 (9.x). The OLE DB Driver for SQL Server is a stand-alone data access application programming interface (API), used for OLE DB, that was introduced in SQL Server 2005 (9.x). OLE DB Driver for SQL Server delivers the SQL OLE DB driver in one dynamic-link library (DLL). Feb 12, 2019 Installing OLE DB Driver for SQL Server as a dependency. It is important not to uninstall OLE DB Driver for SQL Server until all dependent applications are uninstalled. To provide users with a warning that your application depends on OLE DB Driver for SQL Server, use the APPGUID install option in your MSI, as follows.
Important
The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work.
The OLE DB Driver for SQL Server can be used in conjunction with OLE DB Core Services supplied with Windows DAC, but this is not a requirement; the choice to use Core Services or not depends on the requirements of the individual application (for example, if connection pooling is required).
ActiveX Data Object (ADO) applications may use the OLE DB Driver for SQL Server, but it is recommended to use ADO in conjunction with the DataTypeCompatibility connection string keyword (or its corresponding DataSource property). When using the OLE DB Driver for SQL Server, ADO applications may exploit those new features introduced in SQL Server 2005 (9.x) that are available via the OLE DB Driver for SQL Server via connection string keywords or OLE DB properties or Transact-SQL. For more information about the use of these features with ADO, see Using ADO with OLE DB Driver for SQL Server.
OLE DB Driver for SQL Server was designed to provide a simplified method of gaining native data access to SQL Server using OLE DB. It provides a way to innovate and evolve new data access features without changing the current Windows DAC components, which are now part of the Microsoft Windows platform.
While OLE DB Driver for SQL Server uses components in Windows DAC, it is not explicitly dependant on a particular version of Windows DAC. You can use OLE DB Driver for SQL Server with the version of Windows DAC that is installed with any operating system supported by OLE DB Driver for SQL Server.
Different generations of OLE DB Drivers
There are three distinct generations of Microsoft OLE DB providers for SQL Server.
1. Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) still ships as part of Windows Data Access Components. It is not maintained anymore and it is not recommended to use this driver for new development.
2. SQL Server Native Client (SNAC)
Starting in SQL Server 2005 (9.x), the SQL Server Native Client (SNAC) includes an OLE DB provider interface (SQLNCLI) and is the OLE DB provider that shipped with SQL Server 2005 (9.x) through SQL Server 2012 (11.x).
It was announced as deprecated in 2011 and it is not recommended to use this driver for new development. For more information about the SNAC lifecycle and available downloads, refer to SNAC lifecycle explained.
3. Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
OLE DB was undeprecated and released in 2018.
The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). The new provider will be updated with the most recent server features going forward.
Note
To use the new Microsoft OLE DB Driver for SQL Server in existing applications, you should plan to convert your connection strings from SQLOLEDB or SQLNCLI, to MSOLEDBSQL.
In this section
When to use OLE DB Driver for SQL Server
Discusses how OLE DB Driver for SQL Server fits in with Microsoft data access technologies, how it compares to Windows DAC and ADO.NET, and provides pointers for deciding which data access technology to use.
OLE DB Driver for SQL Server features
Describes the features supported by OLE DB Driver for SQL Server.
Building applications with OLE DB Driver for SQL Server
Provides an overview of OLE DB Driver for SQL Server development, including how it differs from Windows DAC, the components that it uses, and how ADO can be used with it.
This section also discusses OLE DB Driver for SQL Server installation and deployment, including how to redistribute the OLE DB Driver for SQL Server library.
System requirements for OLE DB Driver for SQL Server
Discusses the system resources needed to use OLE DB Driver for SQL Server.
OLE DB Driver for SQL Server programming
Provides information about using the OLE DB Driver for SQL Server.
Finding more OLE DB Driver for SQL Server information
Provides additional resources about OLE DB Driver for SQL Server, including links to external resources and getting further assistance.
See also
Updating an application from SQL Server 2005 Native Client
OLE DB how-to topics
In order to take advantage of new features introduced in SQL Server 2005 (9.x) such as multiple active result sets (MARS), query notifications, user-defined types (UDTs), or the new xml data type, existing applications that use ActiveX Data Objects (ADO) should use the OLE DB Driver for SQL Server as their data access provider.
To enable ADO to use new features of recent versions of SQL Server, some enhancements have been made to the OLE DB Driver for SQL Server which extends the core features of OLE DB. These enhancements allow ADO applications to use newer SQL Server features and to consume two data types introduced in SQL Server 2005 (9.x): xml and udt. These enhancements also exploit enhancements to the varchar, nvarchar, and varbinary data types. OLE DB Driver for SQL Server adds the SSPROP_INIT_DATATYPECOMPATIBILITY initialization property to the DBPROPSET_SQLSERVERDBINIT property set for use by ADO applications so that the new data types are exposed in a way compatible with ADO. In addition, the OLE DB Driver for SQL Server also defines a new connection string keyword named DataTypeCompatibility that is set in the connection string.
Note
Existing ADO applications can access and update XML, UDT, and large value text and binary field values using the SQLOLEDB provider. The new larger varchar(max), nvarchar(max), and varbinary(max) data types are returned as the ADO types adLongVarChar, adLongVarWChar and adLongVarBinary respectively. XML columns are returned as adLongVarChar, and UDT columns are returned as adVarBinary. However, if you use the OLE DB Driver for SQL Server (MSOLEDBSQL) instead of SQLOLEDB, you need to make sure to set the DataTypeCompatibility keyword to '80' so that the new data types will map correctly to the ADO data types.
Enabling OLE DB Driver for SQL Server from ADO
To enable the usage of OLE DB Driver for SQL Server, ADO applications will need to implement the following keywords in their connection strings:
Oledb Driver For Sql Server 2008
Provider=MSOLEDBSQLDataTypeCompatibility=80
For more information about the ADO connections string keywords supported in OLE DB Driver for SQL Server, see Using Connection String Keywords with OLE DB Driver for SQL Server.
The following is an example of establishing an ADO connection string that is fully enabled to work with OLE DB Driver for SQL Server, including the enabling of the MARS feature:
Examples
The following sections provide examples of how you can use ADO with the OLE DB Driver for SQL Server.
Retrieving XML Column Data
In this example, a recordset is used to retrieve and display the data from an XML column in the SQL Server AdventureWorks sample database.
Note Aircrack ng tutorial windows.
Ole Db Provider For Sql Server
Recordset filtering is not supported with XML columns. If used, an error will be returned.
Retrieving UDT Column Data
In this example, a Command object is used to execute a SQL query that returns a UDT, the UDT data is updated, and then the new data is inserted back into the database. This example assumes that the Point UDT has already been registered in the database.
Enabling and Using MARS
In this example, the connection string is constructed to enable MARS through the OLE DB Driver for SQL Server, and then two recordset objects are created to execute using the same connection.
In prior versions of the OLE DB provider, this code would cause an implicit connection to be created on the second execution because only one active set of results could be opened per a single connection. Because the implicit connection was not pooled in the OLE DB connection pool this would cause additional overhead. With the MARS feature exposed by the OLE DB Driver for SQL Server, you get multiple active results on the one connection.