Microsoft SQL Server Native Client (referred to hereafter as SNAC) was introduced in SQL Server 2005 and combines an ODBC driver and OLE DB provider in a single DLL. The SQL Server ODBC driver and SQL Server OLE DB provider for earlier versions of SQL Server was delivered via Microsoft Data Access Components (MDAC), recently renamed Windows Data Access Components (WDAC) in Windows Vista and later. SNAC and WDAC coexist and serve different needs. SNAC provides support for the latest SQL Server 2005 features. If you are developing a generic application to operate with any ODBC driver or OLE DB provider (namely, an application which is not taking advantage of provider specific types), then using the Windows (WDAC) integrated SQL Server 2000 versions of the ODBC driver and OLE DB provider is a better choice since they map new SQL Server 2005 types to standard ODBC types that are compatible with SQL Server 2000 and other ODBC drivers. The differences in type behavior are discussed more in the “Differences between driver/provider versions” section below in the document.

With SQL Server 2008, we again wanted to expose new features and data types to applications in the most natural way, and we knew that some of the new features (Table-Valued Parameters, for example) would force fairly major code reorganization that would inevitably introduce behavior differences. Most applications won't be bothered at all by this, but when you take into account the huge number of applications written to use SQL Server, it's inevitable that some might be impacted. In order to minimize disruption when SQL Server is upgraded, we decided that SNAC in SQL Server 2005 (now refered to as SNAC9) and SNAC in SQL Server 2008 (now refered to as SNAC10) should be able to coexist side by side. This means that when SQL Server 2008 (or SNAC10 on a client-only system) is installed, applications already deployed using SNAC9 will see no change at all. They will simply continue to run the way they did before using the version of SNAC they originally were built with, which in this case would be SNAC9.. Switching an application to use SNAC10 requires some intervention and we strongly recommend that the application is thoroughly tested with the new version of SNAC before it is deployed in production environments.

Side by side co-existence is achieved by renaming the ODBC driver name and OLE DB progid (and allocating new classids too). The differences between SNAC9, SNAC10 and WDAC (which contains the SQL Server 2000 versions of the SQL ODBC driver and SQL OLE DB provider) are summarized in the following table:

Property

WDAC

SQL Server Native Client (SNAC9)

SQL Server 2008 Native Client (SNAC10)

ODBC Driver Name

SQL Server

SQL Native Client

SQL Server Native Client 10.0

ODBC Header  file name

odbcss.h

sqlncli.h

sqlncli.h

ODBC Driver DLL

sqlsrv32.dll

sqlncli.dll

sqlncl10.dll

ODBC lib file for BCP APIs

odbcbcp.lib

sqlncli.lib

sqlncli10.lib

ODBC dll for BCP APIs

odbcbcp.dll

sqlncli.dll

sqlncli10.dll

OLE DB PROGID

SQLOLEDB

SQLNCLI

SQLNCLI10

OLE DB Header file name

sqloledb.h

sqlncli.h

sqlncli.h

OLE DB Provider DLL

sqloledb.dll

sqlncli.dll

sqlncli10.dll

Notice that we didn't change the header file name. Since the SNAC9 and SNAC10 SDK files are installed in different paths we can avoid the need to change #include directives in source code, only the include path need be changed. To minimize future code change when SNAC11 comes along, we added macros to the header file to enable applications to automatically pick up the correct version names when they are built for a specific version of SNAC (with the default being the latest version). If you look in sqlncli.h for SNAC10 you'll see things like this:

#if !defined(SQLNCLI_VER)
#define SQLNCLI_VER 1000
#endif

#if SQLNCLI_VER >= 1000
#define SQLNCLI_PRODUCT_NAME_FULL_VER_ANSI
"Microsoft SQL Server Native Client 10.0"
#define SQLNCLI_PRODUCT_NAME_FULL_ANSI
"Microsoft SQL Server Native Client"
#define SQLNCLI_PRODUCT_NAME_SHORT_VER_ANSI
"SQL Server Native Client 10.0"
#define SQLNCLI_PRODUCT_NAME_SHORT_ANSI
"SQL Server Native Client"
...
#else
// SQLNCLI_VER >= 1000
#define SQLNCLI_PRODUCT_NAME_FULL_VER_ANSI "Microsoft SQL Server Native Client"
#define SQLNCLI_PRODUCT_NAME_FULL_ANSI "Microsoft SQL Server Native Client"
#define SQLNCLI_PRODUCT_NAME_SHORT_VER_ANSI "SQL Native Client"
#define SQLNCLI_PRODUCT_NAME_SHORT_ANSI "SQL Native Client"
...

Each version of SNAC will support the version of SQL Server that it ships with, two earlier versions and two later versions, so SQL Server support for the two existing SNAC versions is as follows:

SQL Server Native Client Version

Supported SQL Server Versions

SQL Server Native Client (SNAC9)

Microsoft SQL Server 7.0
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
Next SQL Server version

SQL Server 2008 Native Client (SNAC10)

Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
Next two SQL Server versions

The server version support structure means that there is no rush to change the driver/provider an application uses. Developers can make the changeover on a timescale that makes business sense for them, such as the next major version releases of their applications. 

In a similar way, supported Windows versions may change with each SNAC version to allow for older versions of Windows dropping out of support. For SNAC9 and SNAC10, operating system support is as follows:

SQL Server Native Client Version

Supported Operating Systems

SQL Server Native Client (SNAC9)

Microsoft Windows 2000 Service Pack 4 or later
Microsoft Windows Server 2003 or later
Microsoft Windows XP Service Pack 1 or later
Microsoft Windows Vista
Microsoft Windows 2008 Server

SQL Server 2008 Native Client (SNAC10)

Microsoft Windows Server 2003 SP1 or later
Microsoft Windows XP Service Pack 2 or later
Microsoft Windows Vista
Microsoft Windows 2008 Server

Differences between driver/provider versions

The different driver/provider versions see SQL Server data types differently because SQL Server converts values that it sends to clients to types that are understood by the client version, as summarized in the following table:

Data type

MDAC and
SQL Server Native Client OLE DB
applications (SNAC9 or SNAC10) with DataTypeCompatibility=80

SQL Server Native Client
(SNAC9)

SQL Server 2008 Native Client
(SNAC10)

Types introduced in SQL Server 2005

CLR UDT (<= 8000)

varbinary

udt

udt

varbinary(max)

image

varbinary

varbinary

varchar(max)

text

varchar

varchar

nvarchar(max)

ntext

nvarchar

nvarchar

xml

ntext

xml

xml

Types introduced in SQL Server 2008

CLR UDT (> 8000)

image

varbinary

udt

date

varchar

varchar

date

datetime2

varchar

varchar

datetime2

datetimeoffset

varchar

varchar

datetimeoffset

time

varchar

varchar

time

geometry

image

varbinary

udt

geography

image

varbinary

udt

hierarchyid

image

varbinary

udt

For new types with unlimited size the maximum size is represented as SQL_SS_LENGTH_UNLIMITED (value 0) in ODBC and ~0 in OLE DB. The APIs uses different values because each API has its own convention for representing values of unlimited size (and these conventions pre-date SNAC). Applications which do arithmetic based on the size returned in parameter and result metadata and which don't take account of this special case may misbehave. This is one reason why we recommend that applications moving to new driver/provider versions should not be deployed without thorough testing. Other version differences are described in SQL Server Books Online.

Deciding which driver/provider to use with an application is straighforward once the above is understood.

  • For 3rd party applications designed to work with a wide range of drivers/providers that do not specifically state that they support SNAC (such as Excel or Access, for example), or applications built using ATL or MFC use WDAC
  • For 3rd party applications that specify a particular version of SNAC, use only that version of SNAC, even when a later version is available. If the application is following best practice guidelines SNAC will be redistributed with the application and its installation procedures should configure the correct version of SNAC for its use, so you shouldn't have to worry about this case
  • For your own applications, stay with the driver/provider you are currently using until you reach a convenient point to upgrade and take advantage of new datatypes and features in the lastest version of SNAC
  • If you cannot upgrade the application to use the latest version of SNAC but must adapt to schema changes use the table above to determine how new types will appear to your application