MSFT - Maddog: Can't do a distributed query into a table w/ XML columsn even if you're not referencing the XML column - by moody31415

Status : 


Sign in
to vote
ID 338953 Comments
Status Active Workarounds
Type Bug Repros 23
Opened 4/18/2008 2:22:02 PM
Duplicates 247204 Access Restriction Public


If you have a table with an XML column in it, you can't do a distributed query against that table - even if you're not referencing the XML column in your distributed query.  

SQL should be able to figure out that I'm not referencing the XML column, so just let me do my query already - instead of forcing me to create a seperate view on the server that contains all the same columns except the foolishly offensive XML column. 

Our table looks somewhat like this: 
CREATE TABLE [dbo].[Run](
     [RunID] [int] IDENTITY(1,1) NOT NULL,
     [Title] [varchar](120) NULL,
     [Notes] [text] NULL,
     [StartTime] [datetime] NULL,
     [CodeCoverage] [char](1) NOT NULL,
     [AutoAnalysisOptionsXML] [varchar](max) NULL,
     [TokenXML] [xml] NULL
And we can’t do a distributed query into it. Before we added the xml column, we could. 

Ie, we can’t do ‘select top 10 runid from server.database.dbo.Run’ but we could before the XML column; we get this error: 

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

Other columns like text, varchar(max), etc don’t have this problem, but the XML column blocks this distributed query. 
Sign in to post a comment.
Posted by FullParam on 2/21/2015 at 6:31 PM
Would be nice to get some traction on this. Creating views is not always supported with certain vendor specific databases. (i.e you would break the support agreement)
Posted by jyao on 7/29/2014 at 1:59 PM
@Joachim Hammer, where is your update after even sql server 2014 was released months ago?
Posted by Brain2000 on 6/3/2014 at 9:21 AM
Guys, we are not looking to make XML columns work. At this point, we just want to be able to run queries that do not contain XML columns. The VIEW workaround is weak.
Posted by JackInTBox on 3/3/2014 at 8:55 AM
Well, Forget about the next release. I am able to reproduce this in SQL 2014 CTP2. Its pretty evident that this needs to be fixed. I can understand referencing the XML column causing issues, but I can't wrap my head around why can't I query if I ignore the XML column.
Posted by JediSQL on 10/28/2013 at 1:55 PM
Here's a suggestion:
When I do certain DDL statements, I will get back warnings like "... exceeds th 8060 byte limit... inserts or updates may fail..." How about when columns of certain data types are included/added to a table, there are warnings about the limitations those data type impose?
Posted by JediSQL on 10/28/2013 at 1:46 PM
This behavior is not documented in Books Online at all. I searched the 2012 Books Online and these two topics have no reference to "distributed" or "linked":
XML Data Type and Columns (SQL Server)
xml (Transact-SQL)
Posted by ErnestoMawan on 10/2/2013 at 2:19 AM
Just also ran into this, impressiv to see how far MSFT came in 5 years.
Posted by Adam Machanic on 10/11/2012 at 7:24 AM
This is still an issue in SQL Server 2012. So much for considering it for the next version.
Posted by Microsoft on 4/21/2008 at 2:27 PM

Thanks for the feedback. Since we are getting close to the end of the release we may consider this only for the next version of SQL Server. I will keep you posted.


Joachim Hammer

Program Manager
SQL Server