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!