SQL Server 2005 and 2012

Posted on 2014-07-13
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
    LVL 14

    Accepted Solution


    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

    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

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

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now