.
.
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