Microsoft KB Archive/905006

= Description of the type mapping of the new SQL Server 2005 data types in the OLE DB Provider for SQL Server (SQLOLEDB) and SQL Native Client =

Article ID: 905006

Article Last Modified on 3/11/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-





INTRODUCTION
This article describes the type mapping of the new Microsoft SQL Server 2005 data types in the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and Microsoft SQL Native Client.



MORE INFORMATION
You may use ActiveX Data Objects (ADO) with SQLOLEDB or SQL Native Client to connect to a Microsoft SQL Server 2005 database. When SQLOLEDB or SQL Native Client returns the metadata to client applications, SQLOLEDB or SQL Native Client maps the new SQL Server 2005 data types to the data types that are listed in the following table.

Notice that some differences occur when this data type mapping occurs. The varchar(max), nvarchar(max), and varbinary(max) data types have fewer restrictions than the text, ntext, and image data types. A general application has more flexibility in the Transact-SQL Script statement that uses the new SQL Server 2005 data types. However, an ADO-based application cannot determine the columns that are new data types by examining at the metadata.

Additionally, the metadata can be inconsistent for the new data types. When you use ADO together with SQL Native Client to connect with a SQL Server 2005 database, we recommend that you modify the DataTypeCompatibility connection option to the following value: DataTypeCompatibility=80 For example, if you try to query a column of the XML type from ADO, you receive the ntext data type. Additionally, when you try to use the like operator together with a column of the XML type, you receive an error message that is similar to the following:

Argument data type xml is invalid for argument 1 of like function.

This behavior is by design. This data type mapping makes ADO-based applications continue to work when they experience a column of the new SQL Server 2005 data types.

