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

Question

I have two SQL Servers (running SQL Server 2008) named DATA01 and DATA02. DATA02 has a linked server definition LINK, that points at DATA01, with suitable user mapping set up. On DATA01 there is a database 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 data returned as expected:

SELECT Id FROM LINK.MyDatabase.dbo.T_A;

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

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

The error is

Xml data type is not supported in distributed queries. Remote object 'DATA02.MyDatabase.dbo.T_B' has xml column(s).

And strangely, this command:

SELECT Id FROM LINK.MyDatabase.dbo.T_B;

also gives the same error, even though I'm not SELECTing the xml column! What's going on?

Answer 1

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.

Answer 2

Try this:

• Create a view on the source side with xml cast to nvarchar(max):

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

• You can select it on the destination side with cast to xml

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

This solution works for me in 2008R2.

Answer 3

I found another way of doing this:

1. Create a Linked Server on DATA01, DATA02 or even some third server (could be local).
2. Execute your query using EXEC [linked_server].[sp_executesql].

Why I prefer this method over creating Views or using OPENQUERY?

1. This method does not require to have any permissions on the Linked Server (you don't need to create Views).
2. You could pass a parameters to your query using the sp_executesql syntax (find more information about sp_sqlexecute here). For OPENQUERY you must pass a STRING or TEXT_LEX, witch means that all the values must be typed right into this function.

Here is the example:

DECLARE @UserID UNIQUEIDENTIFIER = ''

DECLARE @SearchForUserParams NVARCHAR(MAX) = N'@UserID UNIQUEIDENTIFIER';  
DECLARE @SearchForUserQuery NVARCHAR(MAX) = 
N'SELECT
    UserID,
    Username,
    Email,
    CONVERT(NVARCHAR(MAX), UserDataXml) AS UserDataXml
FROM User
WHERE UserID = @UserID'

EXEC [linked_server].[dbo].[sp_executesql] 
    @SearchForUserQuery,
    @SearchForUserParams,
    @UserID = @UserID