Skip to content

Latest commit

 

History

History
117 lines (71 loc) · 14 KB

updating-an-application-to-sql-server-native-client-from-mdac.md

File metadata and controls

117 lines (71 loc) · 14 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Update from MDAC
Upgrade from Windows Data Access Components to SQL Server Native Client, which exposes new features of SQL Server 2005 with backward compatibility.
markingmyname
maghan
03/14/2017
sql
native-client
reference
MDAC [SQL Server]
SQLNCLI, vs. MDAC
SQL Server Native Client, vs. MDAC
data access [SQL Server Native Client], vs. MDAC
SQL Server Native Client, updating applications

Updating an Application to SQL Server Native Client from MDAC

[!INCLUDE SQL Server]

Important

[!INCLUDEsnac-removed-oledb-and-odbc]

There are a number of differences between [!INCLUDEssNoVersion] Native Client and Microsoft Data Access Components (MDAC; starting with Windows Vista, the data access components are now called Windows Data Access Components, or Windows DAC). Although both provide native data access to [!INCLUDEssNoVersion] databases, [!INCLUDEssNoVersion] Native Client has been specifically designed to expose the new features of [!INCLUDEssVersion2005], while at the same time maintaining backward compatibility with earlier versions.

The information in this topic helps update your MDAC (or Windows DAC) application to be current with the version of [!INCLUDEssNoVersion] Native Client that was included in [!INCLUDEssVersion2005]. To help you make this application be current with the version of [!INCLUDEssNoVersion] Native Client that shipped in [!INCLUDEssNoVersion], see Updating an Application from SQL Server 2005 Native Client.

Note

[!INCLUDEssNoVersion] Native Client has been removed from [!INCLUDE sssql22-md].

In addition, although MDAC contains components for using OLE DB, ODBC, and ActiveX Data Objects (ADO), [!INCLUDEssNoVersion] Native Client only implements OLE DB and ODBC (although ADO can access the functionality of [!INCLUDEssNoVersion] Native Client).

[!INCLUDEssNoVersion] Native Client and MDAC differ in the other following areas:

  • Users who use ADO to access a [!INCLUDEssNoVersion] Native Client provider may find less filtering functionality than when they accessed a SQL OLE DB provider.

  • If an ADO application uses [!INCLUDEssNoVersion] Native Client and attempts to update a computed column, an error will be reported. With MDAC the update was accepted but ignored.

  • [!INCLUDEssNoVersion] Native Client is a single self-contained dynamic link library (DLL) file. The publicly exposed interfaces have been kept to a minimum, both to ease distribution, as well as to limit security exposure.

  • Only OLE DB and ODBC interfaces are supported.

  • The [!INCLUDEssNoVersion] Native Client OLE DB provider and ODBC driver names are different from those used with MDAC.

  • User-accessible functionality supplied by MDAC components is available when using [!INCLUDEssNoVersion] Native Client. This includes, but is not limited to, the following: connection pooling, ADO support, and client cursor support. When any of these features are used, [!INCLUDEssNoVersion] Native Client supplies only database connectivity. MDAC provides functionality such as tracing, management controls, and performance counters.

  • Applications can use OLE DB core services with [!INCLUDEssNoVersion] Native Client, but if using the OLE DB cursor engine, they should use the data type compatibility option to avoid any potential problems that might arise because the cursor engine has no knowledge of the new [!INCLUDEssVersion2005] data types.

  • [!INCLUDEssNoVersion] Native Client supports access to previous [!INCLUDEssNoVersion] databases.

  • [!INCLUDEssNoVersion] Native Client does not contain XML integration. [!INCLUDEssNoVersion] Native Client supports SELECT ... FOR XML queries, but does not support any other XML functionality. However, [!INCLUDEssNoVersion] Native Client does support the xml data type introduced in [!INCLUDEssVersion2005].

  • [!INCLUDEssNoVersion] Native Client supports configuring client-side network libraries using only connection string attributes. If you need more complete network library configuration, you must use [!INCLUDEssNoVersion] Configuration Manager.

  • [!INCLUDEssNoVersion] Native Client is not compatible with odbcbcp.dll. Applications that use both ODBC and bcp APIs must be rebuilt to link with sqlncli11.lib in order to use [!INCLUDEssNoVersion] Native Client.

  • [!INCLUDEssNoVersion] Native Client is not supported from the Microsoft OLE DB provider for ODBC (MSDASQL). If you are using the MDAC SQLODBC driver with MSDASQL or MDAC SQLODBC driver with ADO, use OLE DB in [!INCLUDEssNoVersion] Native Client..

  • MDAC connection strings allow a Boolean value (true) for the Trusted_Connection keyword. A [!INCLUDEssNoVersion] Native Client connection string must use yes or no.

  • Minor changes have occurred to warnings and errors. Warnings and errors returned by the server now retain the same severity when passed to [!INCLUDEssNoVersion] Native Client. You should ensure you have thoroughly tested your application if you depend on trapping particular warnings and errors.

  • [!INCLUDEssNoVersion] Native Client has stricter error checking than MDAC, which means that some applications that do not conform strictly to the ODBC and OLE DB specifications may behave differently. For example, the SQLOLEDB provider did not enforce the rule that parameter names must start with '@' for result parameters, but the [!INCLUDEssNoVersion] Native Client OLE DB provider does.

  • [!INCLUDEssNoVersion] Native Client behaves differently from MDAC in regards to failed connections. For example, MDAC returns cached property values for a connection that has failed, whereas [!INCLUDEssNoVersion] Native Client reports an error to the calling application.

  • [!INCLUDEssNoVersion] Native Client does not generate Visual Studio Analyzer events, but instead generates Windows tracing events.

  • [!INCLUDEssNoVersion] Native Client cannot be used with perfmon. Perfmon is a Windows tool that can only be used with DSNs that use the MDAC SQLODBC driver included with Windows.

  • When [!INCLUDEssNoVersion] Native Client is connected to [!INCLUDEssVersion2005] and later versions, server error 16947 is returned as a SQL_ERROR. This error occurs when a positioned update or delete fails to update or delete a row. With MDAC when connecting to any version of [!INCLUDEssNoVersion], server error 16947 is returned as a warning (SQL_SUCCESS_WITH_INFO).

  • [!INCLUDEssNoVersion] Native Client implements the IDBDataSourceAdmin interface, which is an optional OLE DB interface that was not previously implemented, but only the CreateDataSource method of this optional interface is implemented. [!INCLUDEssNoteDepFutureAvoid]

  • The [!INCLUDEssNoVersion] Native Client OLE DB provider returns synonyms in the TABLES and TABLE_INFO schema rowsets, with TABLE_TYPE set to SYNONYM.

  • Return values of data type varchar(max), nvarchar(max), varbinary(max), xml, udt, or other large object types can not be returned to client versions earlier than [!INCLUDEssVersion2005]. If you wish to use these types as return values, you must use [!INCLUDEssNoVersion] Native Client.

  • MDAC allows the following statements to be executed at the start of manual and implicit transactions, but [!INCLUDEssNoVersion] Native Client does not. They must be executed in autocommit mode.

    • All full-text operations (index and catalog DDL)

    • All database operations (create database, alter database, drop database)

    • Reconfigure

    • Shutdown

    • Kill

    • Backup

  • When MDAC applications connect to [!INCLUDEssNoVersion], the data types introduced in [!INCLUDEssVersion2005] will appear as [!INCLUDEssVersion2000]-compatible data types as shown in the following table.

    SQL Server 2005 type SQL Server 2000 type
    varchar(max) text
    nvarchar(max) ntext
    varbinary(max) image
    udt varbinary
    xml ntext

    This type mapping affects the values returned for column metadata. For example, a text column has a maximum size of 2,147,483,647, but [!INCLUDEssNoVersion] Native Client ODBC reports the maximum size of varchar(max) columns as SQL_SS_LENGTH_UNLIMITED, and [!INCLUDEssNoVersion] Native Client OLE DB reports the maximum size of varchar(max) columns as 2,147,483,647 or -1, depending on platform.

  • [!INCLUDEssNoVersion] Native Client allows ambiguity in connection strings (for example, some keywords may be specified more than once, and conflicting keywords may be allowed with resolution based on position or precedence) for reasons of backward compatibility. Future releases of [!INCLUDEssNoVersion] Native Client might not allow ambiguity in connection strings. It is good practice when modifying applications to use [!INCLUDEssNoVersion] Native Client to eliminate any dependency on connection string ambiguity.

  • If you use an ODBC or OLE DB call to start transactions, there is a difference in behavior between [!INCLUDEssNoVersion] Native Client and MDAC; transactions will begin immediately with [!INCLUDEssNoVersion] Native Client, but transactions will begin after the first database access using MDAC. This can affect the behavior of stored procedures and batches because [!INCLUDEssNoVersion] requires @@TRANCOUNT to be the same after a batch or stored procedure finishes execution as it was when the batch or stored procedure started.

  • With [!INCLUDEssNoVersion] Native Client, ITransactionLocal::BeginTransaction will cause a transaction to be started immediately. With MDAC the transaction start was delayed until the application executed a statement which required a transaction in implicit transaction mode. For more information, see SET IMPLICIT_TRANSACTIONS (Transact-SQL).

  • You might encounter errors when using [!INCLUDEssNoVersion] Native Client driver with System.Data.Odbc to access a [!INCLUDEssNoVersion] server computer that exposes new, [!INCLUDEssNoVersion]-specific data types or features. System.Data.Odbc provides a generic ODBC implementation and subsequently does not expose vendor specific functionality or extensions. (The [!INCLUDEssNoVersion] Native Client driver is updated to natively support the latest [!INCLUDEssNoVersion] features.) To workaround this issue, you can either revert to MDAC, or migrate to System.Data.SqlClient.

Both [!INCLUDEssNoVersion] Native Client and MDAC support read committed transaction isolation using row versioning, but only [!INCLUDEssNoVersion] Native Client supports snapshot transaction isolation. (In programming terms, read committed transaction isolation using row versioning is the same as read-committed transaction.).

See Also

Building Applications with SQL Server Native Client