I found one issue while coping records from latest version of sql to old version of
sql where xml datatype is not supported using linked server while fetching records
i found following error occurred "Xml data type is not supported in distributed queries. Remote object
'Server1.dbMurli.dbo.TB_Murli' has xml column(s)."
Root Cause : - XML column can not be accessed directly from the remote server.... - Following is the table structure in Remote server and Local server
CREATE TABLE TB_Murli ( ID INT, Column1 VARCHAR(10), [Address] XML )
Solution : So, we can solve this issue as given below...
INSERT TB_Murli SELECT * FROM ( SELECT * FROM OPENQUERY(Server1,'SELECT ID, Column1,CAST([Address] AS NVARCHAR(MAX))
[Addres] FROM dbMurli.dbo.TB_Murli') )AS XUsign the "OPENQUERY"
we can solve the issue...
In Short :-XML is not supported in distributed queries. You could write a passthrough query with OPENQUERY, and cast the XML column to nvarchar(MAX). For instance:
SELECT cast(xmlcol as xml) FROM OPENQUERY(REMOTESVR, 'SELECT cast(xmlcol AS nvarchar(MAX)) FROM db.dbo.tbl')