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