为什么我在SQL链接服务器中使用XML数据类型的列中收到错误？ [英] Why I am getting error in columns with XML data type in SQL linked server?

问题描述

我在Sql中通过链接服务器进行数据存档时遇到了例外。我正在使用动态脚本，我将数据从Live DB提取到Archival DB。

Xml数据类型列的例外情况。

不支持Xml数据类型在分布式查询中。远程对象'LNK_228.FTS_DIB_ARCH2.dbo.CustomerDetails'有xml列。

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

我尝试了什么：



从动态脚本打印的查询

INSERT INTO [LNK_228]。[FTS_DIB_ARCH2] .dbo.Custom erDetails（CustomerDetailID，CustomerId，AccountNo，CustomerName，EmailIds，IsActive，IsDeleted，CreatedBy，CreatedDate，ModifiedBy，ModifiedDate）

SELECT

CustomerDetailID，

CustomerId，

AccountNo，

客户名称，

CONVERT（VARCHAR（3000），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

推荐答案

对于迟到的回复很抱歉，但是如果将XML列转换为不同的数据类型在Openquery中它应该可以工作。



假设EmailID是XML列，以下类似的东西应该有效：

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

错误消息非常清楚 - 分布式查询中不支持XML列：

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

使用分布式查询的准则 [ ^ ]：

有桌子即使查询访问表的非xml列，也无法查询xml列。

Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table.

这已被报告给Microsoft至少两次，但没有任何迹象表明它将被修复：

• MSFT - Maddog：无法对带有XML的表执行分布式查询columsn即使你没有引用XML列| Microsoft Connect [ ^ ]
• 带有xml列的表不能使用分布式（链接服务器）查询进行查询Microsoft Connect [ ^ ]


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[^]
• 在没有XML列的情况下在远程服务器上创建一个视图并查询;
  
  （如果需要查询XML列，则没什么用处！）
• 使用 OPENQUERY [ ^ ]查询远程表。
• 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.

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




这篇关于为什么我在SQL链接服务器中使用XML数据类型的列中收到错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！