SQL Server 2005 and 2012
Posted on 2014-07-13
I created a linked server (on a networked computer/SQL Instance) and am accessing two tables in the a linked server (via a select query) from my local server instance.,
1. The two table query ("pack" table and "material" table with INNER JOIN) initially failed with this error msg:
Msg 9514, Level 16, State 1, Line 2
Xml data type is not supported in distributed queries. Remote object 'MYLINKEDSERVER.production.dbo.material' has xml column(s).
I tried this in SQL Server 2005 and SQL Server 2012 with same results...
2. I looked at the material table and it does indeed have an XML datatype column although I am not querying this column at all.
3. As a test I modified the SELECT statement to only access the pack table (has no XML datatype column). This select statement works fine.
After some research it appears that distributed queries do not support XML data even if you explicitly SELECT only non-XML data columns. I think I read that the entire table gets copied over in a distributed query,
Does anyone know how to get around this limitation without modifying the Linked (remote) server in any way?