XML Columns Can’t Travel Across Links

One of my vendors upgraded their application recently and one of my reports that monitors the application stopped working.  I connect to the vendor’s base table via a linked server and report back any exceptions I’m interested in.  My report was returning the following error:

Msg 9514, Level 16, State 1, Line 11
Xml data type is not supported in distributed queries. Remote object ‘[ServerName].[DatabaseName].[SchemaName].[TableName]’ has xml column(s).

I disovered that my vendor modified the base table schema adding an XML type column and even though my query worked fine from the vendor’s database server and didn’t reference the XML column, it wouldn’t work across the linked server.

My original query looked something like:

SELECT  ColA
      , ColB
      , ColC
FROM    [LinkedServerName].[DatabaseName].[SchemaName].[TableName]

In order to work around the issue I used OPENQUERY:

SELECT  *
FROM    OPENQUERY([LinkedServerName],
					'SELECT  ColA
					       , ColB
					       , ColC
					 FROM    [SchemaName].[TableName]'
				  )

Problem solved … NEXT!

Leave a Reply

Your email address will not be published. Required fields are marked *