问题描述
我有两个名为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;
错误是
奇怪的是,该命令:
SELECT Id FROM LINK.MyDatabase.dbo.T_B;
也会给出相同的错误,即使我没有SELECT
使用xml列!这是怎么回事?
also gives the same error, even though I'm not SELECT
ing 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:
在您的示例中,这将涉及向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;
不起作用.
SELECT * from OPENQUERY (... )
此方法的优点是不需要对源数据库;缺点是不再可能对本地数据和链接数据使用标准的四部分命名.这查询看起来像
This method has the advantage of not requiring any change to thesource database; the downside is that it is no longer possible touse standard four-part naming for both local and linked data. Thequery 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 (alongwith 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数据时?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!