Description of the type mapping of the new SQL Server 2005 data types in Microsoft Data Access Components (901039)

MORE INFORMATION

When you use Microsoft SQL Server ODBC Driver (SQLODBC) to connect to a SQL Server 2005 database, MDAC maps the new SQL Server 2005 data types to the following types of metadata.

SQL Server data type	MDAC maps to
XML	ntext
user-defined type (UDT)	varbinary
varchar(max)	text
nvarchar(max)	ntext
varbinary(max)	image

Note This data type mapping does not occur when you use Microsoft SQL Native Client to connect to a SQL Server 2005 database.

For example, if you query a column of the XML data type from an MDAC application, you receive the ntext data type. Additionally, when you try to use the like operator together with a column of the XML data type, you receive an error message that is similar to the following: This behavior is by design. This data type mapping makes an MDAC application continue to work when the application has to use a column of data that contains one of the new SQL Server 2005 data types.

Notice that there are some differences between the SQL Server 2005 data types and the data types to which MDAC maps. The varchar(max) data type, the nvarchar(max) data type, and varbinary(max) data type have fewer restrictions than the text data type, the ntext data type, and the image data type.

When you use the new SQL Server 2005 data types, a general application has more flexibility in the statement that is in the Transact-SQL script. However, an MDAC application cannot determine which columns contain the new data types by examining the metadata. Additionally, the metadata may be inconsistent for the new data types.