Monday 7 November 2011

How to query the table on remote sql server which has xml column(s)

Problem: you need to run a query on a table on remote ("linked") server. The table has one or more xml columns. Your query doesn't depend on any xml columns, however it still fails with an error Xml data type is not supported in distributed queries. Remote object 'SERVERNAME.DBNAME.dbo.tTable' has xml column(s).


Failing Query Example: SELECT TOP 10 SomeID FROM [SERVERNAME].[DBNAME].[dbo].tTable


Solution: Use OPENQUERY: SELECT * FROM OPENQUERY([SERVERNAME], 'SELECT TOP 10 SomeID FROM [DBNAME].[dbo].tTable') AS a


Thanks to: the bug (sorry, "limitation") in Sql Server 2005 and 2008





No comments:

Post a Comment