Why am I getting the "Xml data type not supported in distributed requests" error when querying a linked server for data other than xml?

Question

I have two SQL servers (running SQL Server 2008) named DATA01 and DATA02 . DATA02 has a LINK linked server definition that points to DATA01 , with the appropriate user mapping setting. DATA01 has a MyDatabase containing these two tables:

 CREATE TABLE T_A ( Id int ) CREATE TABLE T_B ( Id int, Stuff xml ) 

When I run this command from DATA02 , I get the data as expected:

 SELECT Id FROM LINK.MyDatabase.dbo.T_A; 

However, when I run this command from DATA02 , I get an error message:

 SELECT Id, Stuff FROM LINK.MyDatabase.dbo.T_B; 

Mistake

The Xml data type is not supported in distributed requests. The remote object "DATA02.MyDatabase.dbo.T_B" has an xml column.

And oddly enough, this command:

 SELECT Id FROM LINK.MyDatabase.dbo.T_B; 

also gives the same error, although I am not a SELECT xml column! What's happening?

Answer 1

This is a flaw in SQL Server. The mere existence of the xml column of the table does not allow it to participate in distributed queries (for example, it is requested through a connected connection to the server). This one is mentioned in the documentation , although not particularly noticeable. You can see the main connection error report here and a similar report here . The latter gives two workarounds:

• Create view [a] without XML column (s) on the remote server and request this.

  In your example, this will include adding a view to MyDatabase it looks like this:

   CREATE VIEW V_T_B AS SELECT Id FROM T_B; 

  Then you can request this view by reference to get Id data. Please note that something like

   SELECT Id FROM ( SELECT Id FROM T_B ) T_B; 

  does not work.

• Use pass-through request in form

   SELECT * from OPENQUERY (... ) 

  This method has the advantage that no changes to the source database are required; the disadvantage is that it is no longer possible to use standard four-digit naming for local and related data. the request will look like

   SELECT Id FROM OPENQUERY(DATA02, 'SELECT Id FROM T_B') T_B; 

  Note that if you really want xml data, this method (along with casting to and from a non-XML type):

   SELECT Id, CAST(Stuff AS XML) Stuff FROM OPENQUERY(DATA02, 'SELECT Id, CAST(Stuff AS nvarchar(max)) Stuff FROM T_B') T_B; 

Note that the error was first reported in SQL Server 2005 and remains uncommitted in SQL Server 2014.

Answer 2

Try the following:

• Create a source side view using xml for nvarchar (max):

CREATE VIEW vXMLTest as a SELECT cast (Stuff as nvarchar (max)) as STUFF FROM T_B

• You can select it on the destination side by clicking on xml

SELECT Cast (material as XML) as FROM OPENQUERY material (DATA02, 'SELECT Stuff FROM vXMLTest')

This solution works for me in 2008R2.