Error: Xml data type is not supported in distributed queries. Remote object '<ServerIP>\<ServerInstance>.<DatabaseName>.<Owner>.<TableName>' has xml column(s).
Problem:
This error occurs when trying to select data from table on different server (using linked server) containing XML data type (even though the column with XML data type is not selected).
Solution:
Create a view on the remote server, and then select the view instead of directly selecting from the table containing XML data type.
e.g.
<ServerInstance> = 10.0.0.101
<DatabaseName> = DB1
<Owner> = dbo
<TableName> = Table1
Instead of using:
Create a view on the remote server:
And call the view:
Problem:
This error occurs when trying to select data from table on different server (using linked server) containing XML data type (even though the column with XML data type is not selected).
Solution:
Create a view on the remote server, and then select the view instead of directly selecting from the table containing XML data type.
e.g.
<ServerInstance> = 10.0.0.101
<DatabaseName> = DB1
<Owner> = dbo
<TableName> = Table1
Instead of using:
SELECT * FROM [10.0.0.101.DB1].dbo.Table1
Create a view on the remote server:
CREATE VIEW vGetTable1
AS
SELECT * FROM dbo.Table1
And call the view:
SELECT * FROM [10.0.0.101.DB1].dbo.vGetTable1
