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...
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_DotnetFundaSolution :
So, we can solve this issue as given below...
SELECT * FROM
SELECT * FROM OPENQUERY(Server1,'SELECT ID, Column1, CAST([Address] AS NVARCHAR(MAX)) [Addres] FROM DotnetFunda.dbo.TB_DotnetFunda')
Usign the "OPENQUERY" we can solve the issue...