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