Why do I get the error “Xml data type is not supported in distributed queries” when querying a linked server for non-xml data?

by

This is a deficiency within SQL Server. The mere existence of an xml column on the table prevents it from participating in distributed queries (eg being queried through a linked server connection). This is mentioned in this ‘retired’ documentation. There doesn’t appear to be any mention of this in the current version’s documentation.

There used to be relevant bug reports on Microsoft Connect, but that’s now been ‘retired’ in favour of the Azure feedback forums. This feedback item is Closed with an instruction to “Please submit feedback directly from the product documentation”, which would be fine if the product documentation actually mentioned this. This other feedback item includes commentary migrated from Connect, and has the status ‘Unplanned’.

One of the Connect bug reports that used to exist gave two workarounds:

  1. Create [a] view without the XML column(s) on remote server and query that.

    In your example, this would involve adding a view to MyDatabase
    that looks like this:

    CREATE VIEW V_T_B AS SELECT Id FROM T_B;

    You could then query this view through the link to get the Id
    data. Note that something like

    SELECT Id FROM ( SELECT Id FROM T_B ) T_B;

    doesn’t work.

  2. Use a pass-through query in the form

    SELECT * from OPENQUERY (... )

    This method has the advantage of not requiring any change to the
    source database; the downside is that it is no longer possible to
    use standard four-part naming for both local and linked data. The
    query would look like

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

    Note that if you actually do want the xml data, this method (along
    with casting to and from a non-xml datatype) will be required :

     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 bug was first reported in SQL Server 2005, and remains unfixed in SQL Server 2017. I haven’t yet been able to check SQL Server 2019.

Leave a Comment

tech