Archive

Posts Tagged ‘OLE DB’

SQL Server: ODBC, OLEDB and SQL Native Client Providers

December 22, 2009 Leave a comment

 

We have different providers to use (Microsoft OLE DB Provider for SQL Server or SQL Native Client) while creating SQL Server linked servers, or connecting to other sources like Excel, Access DB etc. This post gives brief overview of each of these providers and the difference between them.

ODBC is the programming API for connecting to any database management systems independent of vendor, operating system and programming languages. This was the first driver software designed for interacting with different Database Management Systems.

OLE DB is the Microsoft specific Data Access API for connecting to various Database Management Systems and extends the functionality of ODBC. This API was built using Microsoft COM technology and is part of the MDAC (Microsoft Data Access Components) which is installed with the Operating System or can be installed separately. MDAC includes the support for OLE DB, ODBC and ADO.

Till the SQL Server 2000 the SQL OLE DB and SQL ODBC connectivity was provided by MDAC package. But from SQL Server 2005 we have two different APIS

1. The Original MDAC which comes with OS

2. New API for SQL OLE DB, SQL ODBC and ADO which is called the SQL Native Client.

SQL Native Client combines the SQL OLE DB and SQL ODBC in MDAC into one DLL (SQLNCLI.DLL) which is shipped with the SQL Server and not part of the Operating System. So there is no dependency on the Operating System. As this DLL is shipped with the SQL Server every new release of the product might have a new version of SQL Native Client.

Now if you see the list of providers in SQL Server 2008 linked server creation wizard below; You can figure out that the providers listed with the prefix Microsoft are from the MDAC and SQL Native Client 10.0 is the SQL Server 2008 provider for SQL OLE DB and ODBC.

 

image

 

To create a linked server to SQL Server 2000 database we should use MDAC provider (Microsoft OLE DB Provider for SQL Server)  and to SQL Server 2005 database we can use SQL Native Client provider or MDAC provider if you are not plannng to leverage the following new features offered by SQL Native Client.

 

  • MARS (Multiple Active Result Sets in a single connection)
  • XML Data Types
  • CLR User Defined Types
  • Asynchronous Data Access
  • Database Mirroring
  • Snapshot Isolation

Is there a Problem to existing Applications that use MDAC SQL OLE DB or ODBC connectivity on SQL Server 2005?

For the applications that use SQL ODBC and SQL OLE DB from MDAC there is no impact. But if you want to utilize the new features in SQL Server 2005 like UDTs or XML Types you need to use the SQL Native Client otherwise your applications should work fine.

 

For more information on the MDAC architecture you can go here.

🙂 ranjith 🙂

Advertisements