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.