为什么会出现错误“分布式查询不支持Xml数据类型"?在链接服务器上查询非xml数据时? [英] Why do I get the error "Xml data type is not supported in distributed queries" when querying a linked server for non-xml data?

问题描述

我有两个名为DATA01和DATA02的SQL Server(运行SQL Server 2008). DATA02具有链接的服务器定义LINK，该服务器指向DATA01，并设置了适当的用户映射.在DATA01上，有一个数据库MyDatabase包含以下两个表:

I have two SQL Servers (running SQL Server 2008) named DATA01 and DATA02. DATA02 has a linked server definition LINK, that points at DATA01, with suitable user mapping set up. On DATA01 there is a database MyDatabase containing these two tables:

CREATE TABLE T_A (
    Id int
)

CREATE TABLE T_B (
    Id int,
    Stuff xml
)

当我从DATA02运行此命令时，我得到的数据按预期返回:

When I run this command from DATA02, I get data returned as expected:

SELECT Id FROM LINK.MyDatabase.dbo.T_A;

但是，当我从DATA02运行此命令时，出现错误:

However, when I run this command from DATA02, I get an error:

SELECT Id, Stuff FROM LINK.MyDatabase.dbo.T_B;

错误是

分布式查询不支持

Xml数据类型.远程对象"DATA02.MyDatabase.dbo.T_B"具有xml列.

Xml data type is not supported in distributed queries. Remote object 'DATA02.MyDatabase.dbo.T_B' has xml column(s).

奇怪的是，该命令:

SELECT Id FROM LINK.MyDatabase.dbo.T_B;

也会给出相同的错误，即使我没有SELECT使用xml列！这是怎么回事?

also gives the same error, even though I'm not SELECTing the xml column! What's going on?

推荐答案

这是SQL Server的不足之处.表格中xml列的仅 existance (存在)阻止它参与分布式查询(例如，通过链接服务器连接进行查询).在文档中提到了 ，尽管不是特别突出.您可以看到此处类似的报告.后者提供了两种解决方法:

This is a deficiency within SQL Server. The mere existence of an xml column on the table prevents it from participating in distributed queries (eg being queried through a linked server connection). This is mentioned in the documentation, though not particularly prominently. You can see the main Connect bug report here, and a similar report here. The latter gives two workarounds:

在远程服务器上创建不包含XML列的[a]视图并对其进行查询.

Create [a] view without the XML column(s) on remote server and query that.

在您的示例中，这将涉及向MyDatabase添加一个视图 看起来像这样:

In your example, this would involve adding a view to MyDatabase that looks like this:

CREATE VIEW V_T_B AS SELECT Id FROM T_B;

然后您可以通过链接查询此视图以获取Id 数据.请注意，类似

You could then query this view through the link to get the Id data. Note that something like

SELECT Id FROM ( SELECT Id FROM T_B ) T_B;

不起作用.

使用以下形式的传递查询

Use a pass-through query in the form

SELECT * from OPENQUERY (... )

此方法的优点是不需要对 源数据库；缺点是不再可能 对本地数据和链接数据使用标准的四部分命名.这 查询看起来像

This method has the advantage of not requiring any change to the source database; the downside is that it is no longer possible to use standard four-part naming for both local and linked data. The query would look like

SELECT Id FROM OPENQUERY(DATA02, 'SELECT Id FROM T_B') T_B;

请注意，如果您实际上要做想要xml数据，则此方法(以及 与非XML数据类型之间的转换)将是必需的:

Note that if you actually do want the xml data, this method (along with casting to and from a non-xml datatype) will be required :

SELECT Id, CAST(Stuff AS XML) Stuff 
FROM OPENQUERY(DATA02, 'SELECT Id, CAST(Stuff AS nvarchar(max)) Stuff 
                        FROM T_B') T_B;

请注意，该错误最早是在SQL Server 2005中报告的，而在SQL Server 2014中仍未修复.

Note that the bug was first reported in SQL Server 2005, and remains unfixed in SQL Server 2014.

这篇关于为什么会出现错误“分布式查询不支持Xml数据类型"?在链接服务器上查询非xml数据时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！