Home Dashboard Directory Help
Search

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: 

Active


70
0
Sign in
to vote
Type: Bug
ID: 338953
Opened: 4/18/2008 2:22:02 PM
Access Restriction: Public
Duplicates: 247204
1
Workaround(s)
view
17
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
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
Josh,

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.

Regards,

Joachim Hammer

Program Manager
SQL Server
Sign in to post a workaround.
Posted by moody31415 on 3/4/2014 at 3:51 PM
As implied by the text in the description, you can work around this issue by creating a view without the offending xml column, and then doing a distributed query against the view.