Why I am getting error in columns with XML data type in SQL linked server?

Question

0.00/5 (No votes)

See more:

I am getting exception of on doing data Archival via Linked Server in Sql. I am using a dynamic script in which I am fetching data from a Live DB to Archival DB.
The exception is for Xml datatype columns.
Xml data type is not supported in distributed queries. Remote object 'LNK_228.FTS_DIB_ARCH2.dbo.CustomerDetails' has xml column(s).

SQL

INSERT INTO [LNK_228].[FTS_DIB_ARCH2].dbo.CustomerDetails(CustomerDetailID,CustomerId,AccountNo,CustomerName,convert(varchar(max),EmailIds),IsActive,IsDeleted,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate)                  
			SELECT CustomerDetailID,CustomerId,AccountNo,CustomerName,convert(varchar(max),EmailIds),IsActive,IsDeleted,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate FROM  FTS_DIB_ARCH_TEST .dbo.CustomerDetails

What I have tried:

Query printed from the dynamic script
INSERT INTO [LNK_228].[FTS_DIB_ARCH2].dbo.CustomerDetails(CustomerDetailID,CustomerId,AccountNo,CustomerName,EmailIds,IsActive,IsDeleted,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate)
SELECT
CustomerDetailID,
CustomerId,
AccountNo,
CustomerName,
CONVERT(VARCHAR(3000),EmailIds)
,IsActive,IsDeleted,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate FROM FTS_DIB_ARCH_TEST .dbo.CustomerDetails

Posted 26-Apr-16 4:25am

ranio

Updated 28-May-19 12:37pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 14473678 · Answer 1 · 2019-05-28T12:37:00

Sorry for the late reply, but if you cast the XML columns as a different datatype within the Openquery it should work.

Asuming EmailIDs to be the XML column, something like the following should work:

INSERT INTO OPENQUERY([LNK_228], 'SELECT CustomerDetailID, CustomerId, AccountNo, CustomerName, CAST(EmailIds AS VARCHAR(MAX)) AS EmailIds2, IsActive, IsDeleted, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate FROM [FTS_DIB_ARCH2].dbo.CustomerDetails')
SELECT CustomerDetailID, CustomerId, AccountNo, CustomerName, EmailIds2, IsActive, IsDeleted, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate 
FROM FTS_DIB_ARCH_TEST.dbo.CustomerDetails

Richard Deeming · Answer 2 · 2016-04-26T08:15:00

The error message is pretty clear - XML columns are not supported in distributed queries:

Guidelines for Using Distributed Queries[^]:
Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table.

This has been reported to Microsoft as bug at least twice, but there is no indication that it will ever be fixed:

MSFT - Maddog: Can't do a distributed query into a table w/ XML columsn even if you're not referencing the XML column | Microsoft Connect[^]
Tables with xml columns cannot be queried using distributed (llinked server) queries | Microsoft Connect[^]

There are two workarounds suggested in this SO thread[^]:

Create a view on the remote server without the XML column(s) and query that;
(Not much use if you need to query the XML columns!)
Use OPENQUERY[^] to query the remote table.

I haven't tried it, but the documentation suggests that something like this should work:

SQL

INSERT INTO OPENQUERY([LNK_228], 'SELECT CustomerDetailID, CustomerId, AccountNo, CustomerName, EmailIds, IsActive, IsDeleted, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate FROM [FTS_DIB_ARCH2].dbo.CustomerDetails')
SELECT CustomerDetailID, CustomerId, AccountNo, CustomerName, EmailIds, IsActive, IsDeleted, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate 
FROM FTS_DIB_ARCH_TEST.dbo.CustomerDetails

Why I am getting error in columns with XML data type in SQL linked server?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0