SQL Server 2005 and 2012

Posted on 2014-07-13
Medium Priority
Last Modified: 2014-10-07
Hello Experts,

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?

Question by:Saxitalis
  • 2
LVL 15

Accepted Solution

Vikas Garg earned 1500 total points
ID: 40193794

You can try one of these

1. Create a view which should not have column with XML Data Type

2. Convert the Column which has XML Data type to Nvarchar(max) or Nvarbinary(max)

Hope this will help you to resolve your problem.

Author Comment

ID: 40194979
Thanks for the reply.

1. I am not calling the XML column in my view query as I do not need data from it.

2. I added the xml data column to my query with CONVERT(Nvarchar(MAX),[xmldata]) AS [xmldata] but get same error:

Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object 'INNOVAREMOTE05.Innova.dbo.proc_materials' has xml column(s).

Author Closing Comment

ID: 40366162
Thanks - this helped me get on the right track - I had to convert all data to varchar in a temp table first...

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question