Updating an Application to SQL Native Client from MDAC

  • Article

There are a number of differences between SQL Native Client and Microsoft Data Access Components (MDAC). Although both provide native data access to SQL Server databases, SQL Native Client has been specifically designed to expose the new features of SQL Server 2005, while at the same time maintaining backward compatibility with earlier versions.

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

SQL Native Client and MDAC differ in the other following areas:

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

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

  • SQL 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 SQL Native Client OLE DB provider and ODBC driver names are different from those used with MDAC.

  • SQL Native Client does not include user-accessible functionality features supplied by the core MDAC components. This includes but is not limited to connection pooling, ADO support, memory management, and client cursor support. This functionality is exposed via the underlying MDAC operating system installation.

  • Applications can use OLE DB core services with SQL 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 SQL Server 2005 data types.

  • SQL Native Client supports access to previous SQL Server databases starting with SQL Server 7.0 and newer versions.

  • SQL Native Client does not contain XML integration. SQL Native Client supports SELECT … FOR XML queries, but does not support any other XML functionality. However, SQL Native Client does support the new SQL Server 2005 xml data type.

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

  • SQL Native Client is not compatible with odbcbcp.dll. Applications that use both ODBC and bcp APIs must be rebuilt to link with sqlncli.lib in order to use SQL Native Client.

  • SQL Native Client is not supported from deprecated MDAC components, for example, the Microsoft OLE DB provider for ODBC (MSDASQL). For more information regarding deprecated MDAC components, see the Data Access Technologies Roadmap.

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

  • SQL 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 SQL Native Client OLE DB provider does.

  • SQL 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 SQL Native Client reports an error to the calling application.

  • SQL Native Client does not generate Visual Studio Analyzer events, but instead generates Windows tracing events.

  • SQL 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 SQL Native Client is connected to SQL Server 2005, 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 SQL Server 2000 and earlier versions, and with MDAC when connecting to any version of SQL Server, server error 16947 is returned as a warning (SQL_SUCCESS_WITH_INFO).

  • SQL 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.

  • The SQL 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 SQL Server 2005. If you wish to use these types as return values, you must use SQL Native Client.

  • MDAC allows the following statements to be executed at the start of manual and implicit transactions, but SQL 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 SQL Server 2005, the new data types provided by SQL Server 2005 will appear as SQL Server 2000-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 SQL Native Client ODBC reports the maximum size of varchar(max) columns as SQL_SS_LENGTH_UNLIMITED, and SQL Native Client OLE DB reports the maximum size of varchar(max) columns as 2,147,483,647 or -1, depending on platform.

  • SQL 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 SQL Native Client will not allow ambiguity in connection strings. It is good practice when modifying applications to use SQL 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 SQL Native Client and MDAC; transactions will begin immediately with SQL Native Client, but transactions will begin after the first database access using MDAC. This can affect the behavior of stored procedures and batches because SQL Server requires @@TRANCOUNT to be the same after a batch or stored procedure finishes execution as it was when the batch or stored procedure started. See Rollbacks and Commits in Stored Procedures and Triggers for more information.

  • With SQL 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).

Both SQL Native Client and MDAC support read committed transaction isolation using row versioning , but only SQL Native Client supports snapshot transaction isolation. (In programming terms, ead committed transaction isolation using row versioning is the same as read-committed transaction.) For more information, see Choosing Row Versioning-based Isolation Levels.

See Also

Other Resources

Building Applications with SQL Native Client

Help and Information

Getting SQL Server 2005 Assistance