[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query Not Working After XML Data Type Column Added

Posted on 2011-04-21
3
Medium Priority
?
2,113 Views
Last Modified: 2012-05-11
Hello EE,

An XML column has been added to to a table I frequently query using SSMS and I am now receiving the message below.  This happens when I SELECT * or just select the non-xml fields.  What is the workaround?  I am running SQL Server 2005, but connecting via SSMS 2008.

Thank you,
LVBarnes

Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object 'Server.Database.dbo.Table' has xml column(s).
0
Comment
Question by:Lawrence Barnes
3 Comments
 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 1000 total points
ID: 35441717
0
 
LVL 2

Accepted Solution

by:
Sandeepratan earned 1000 total points
ID: 35443290
1. You cannot use a table or view that contains xml or clr type as 4-part name in your query
2. You need to cast the column to either nvarchar(max) or varbinary(max) or other appropriate type to use
3. If you have a table that has xml type for example then you need to create a view that contains all columns other than xml and query it instead. Or you can issue a pass-through query using OPENQUERY with the appropriate columns only.
ReplyQuote

Here is a work around:

SELECT

      Cast(a.XML_Data as XML) as XML_Data

FROM

      OPENQUERY([LINKED SERVER NAME HERE],'

          SELECT

            Cast(XML_Data as Varchar) as XML_Data
         FROM
            [DATABASE NAME].[SCHEMA].[TABLE NAME]'
) a


Basically, the data is queried on the remote server, converts the XML data to a varchar, sends the data to the requesting server and then reconverts it back to XML.
0
 
LVL 5

Author Closing Comment

by:Lawrence Barnes
ID: 35443885
Thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 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