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:
Create [a] view without the XML column(s) on remote server and query that.
In your example, this would involve adding a view to
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
data. Note that something like
SELECT Id FROM ( SELECT Id FROM T_B ) T_B;
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.