SQL Server: Xml data type is not supported in distributed queries. Remote object 'Server1....

Posted by PandianS under Error and Solution on 12/17/2010 | Points: 10 | Views : 24003 | Status : [Member] [MVP] | Replies : 1

.
.
I want to copy the records from Remote server database to current server(The table already created in current server). Following are the server specification

Remote Server Name : Server1
Remote Server Database : DotnetFunda
Table : TB_DotnetFunda
Linked Server Name : Server1 (Linked server to this remote server already defined in current server)

Now, I want to copy the Server1.DotnetFunda.dbo.TB_DotnetFunda records to current server.

When I try to execute the following script...
INSERT TB_DotnetFunda
SELECT * FROM Server1.DotnetFunda.dbo.TB_DotnetFunda WITH (NOLOCK)
The following error occurred

"Xml data type is not supported in distributed queries. Remote object 'Server1.DotnetFunda.dbo.TB_DotnetFunda' 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_DotnetFunda
(
ID INT,
Column1 VARCHAR(10),
[Address] XML
)
Solution :
So, we can solve this issue as given below...
INSERT TB_DotnetFunda
SELECT * FROM
(
SELECT * FROM OPENQUERY(Server1,'SELECT ID, Column1, CAST([Address] AS NVARCHAR(MAX)) [Addres] FROM DotnetFunda.dbo.TB_DotnetFunda')
)AS X
Usign the "OPENQUERY" we can solve the issue...

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions



Responses

Posted by: Vuyiswamb on: 12/19/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
nice thanks for sharing

Thank you for posting at Dotnetfunda
[Administrator]

PandianS, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response