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.
Collapse this tableExpand this table
| SQL Server data type | MDAC maps to |
| XML | ntext |
| user-defined type (UDT) | varbinary |
| varchar(max) | text |
| nvarchar(max) | ntext |
| varbinary(max) | image |
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:
Argument data type xml is invalid for argument
1 of like function.
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.