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 (905006)

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.

SQL Server 2005 data type	New data type
XML	ntext
user-defined type	varbinary
varchar(max)	text
nvarchar(max)	ntext
varbinary(max)	image

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: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.