ADO cannot access SQL Server XML type column using native client (SQLNCLI)

Question

i am using ADO, and one of the "native" drivers (e.g. SQLNCLI, SQLNCLI10, SQLNCLI11) to connect to SQL Server (rather than the legacy SQLOLEDB driver).

ADO does not understand the XML SQL Server data type that the native drivers expose:

field: ADOField;

field := recordset.Fields.Items["SomeXmlColumn"];

Attempting to access field.Value throws an EOleException:

• Source: Microsoft Cursor Engine
• ErrorCode: 0x80040E21 (E_ITF_0E21)
• Message: Multiple-step operation generated errors. Check each status value

The native client drivers (e.g. SQLNCLI, SQLNCLI10, SQLNCLI11) present an Xml data type to ADO as

field.Type_ = 141 //???

while the legacy SQLOLEDB driver presents an Xml data type to ADO as adLongVarWChar, a unicode string:

field.Type_ = 203 //adLongVarWChar

And the VARIANT contained in field.Value is a WideString (technically known as a BSTR):

TVarData(field.Value).vtype = 8 //VT_BSTR

Seems to me that this is a bug in ADO (Windows 7 SP1), and not something i can fix.

How can i fix it?

Bonus Reading

• Delphi with SQL Server: OLEDB vs. Native Client drivers

Spandau

Are you tried adding the DataTypeCompatibility keyword to the connection string? – Custom

Phrase that in the form of an answer, RRUZ, and you have yourself an accept. (Because i have, and it fixes it). i'm gonna go steal credit on every other question on stack overflow where someone's getting 0x80040E21 when using ADO with SQL Server! – Spandau

Answer 1

The MSDN documentation says

To enable ADO to use new features of recent versions of SQL Server, some enhancements have been made to the SQL Server Native Client OLE DB provider which extends the core features of OLE DB. These enhancements allow ADO applications to use newer SQL Server features and to consume two data types introduced in SQL Server 2005: xml and udt. These enhancements also exploit enhancements to the varchar, nvarchar, and varbinary data types. SQL Server Native Client adds the SSPROP_INIT_DATATYPECOMPATIBILITY initialization property to the DBPROPSET_SQLSERVERDBINIT property set for use by ADO applications so that the new data types are exposed in a way compatible with ADO. In addition, the SQL Server Native Client OLE DB provider also defines a new connection string keyword named DataTypeCompatibility that is set in the connection string.

So to enable the use of new SQL Server features in the Native Client, you need to add the following keywords in the connection string:

Provider=SQLNCLI11
DataTypeCompatibility=80

where DataTypeCompatibility:

Specifies the mode of data type handling to use. Recognized values are "0" for provider data types and "80" for SQL Server 2000 data types.

Custom