Click here to Skip to main content
14,329,915 members
Rate this:
Please Sign up or sign in to vote.
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).

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
Updated 28-May-19 12:37pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

The error message is pretty clear - XML columns are not supported in 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:

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:
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
   
Comments
ranio 27-Apr-16 0:52am
   
sill the exception of Xml data type is not supported in distributed queries. Remote object 'OPENQUERY' has xml column(s) is coming on executing above script

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100