Thursday, November 8, 2012

Xml data type is not supported in distributed queries. Remote object


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')
 

No comments:

Post a Comment