Recently while working with data migration,got an error while running a following query where Server2 have beed added as Li nked server.
SELECT * from Server1.Database1.dbo.Table1 WHERE Column1 not in (SELECT Column1 from Server2.Database2.dbo.Table1) go--ERR or raised while run above query "XML data type was not supported in distributed queries. Remote object ' Server2.Database2.dbo.Table1 ' has XML column (s). "
Server2.Database2.dbo.Table1 objects has another column named ' Column2 ' had XML datatype and we have not used it anywhere In query. We used ' column1 ' of that table only and it have not XML datatype, even it has raised error. For the solution to avoid such error I had revised query as following.
SELECT * from Server1.Database1.dbo.Table1 WHERE Column1 not in (SELECT * from OPENQUERY (Server2, ' SELECT Column1 from Database2.dbo.Table1 ') linked
This is the information that is found on the Internet
According to this, I first convert the XML into nvarchar, and turn back to change into XML
SELECT Top [Internal database id],[internal database Search id],[watch list Code],[watch list Description], CONVERT (xml, CAST ([watch list record Detail] as nvarchar(max))) [Watch list record Detail] from OPEN QUERY( [172.16.4.169] ,
'SELECT top [Internal database ID], [Internal database Search id],[watch list Code],[watch list Description], CAST (Watch List Record Detail] as nvarchar (max)) [Watch List Record Detail] from [VERIDOCSECCM]. [dbo]. [Vml_view_watchlisthits]')
SQL Server Error "XML data type is not supported in distributed Queries" and workaround for it