SQL Server空间和链接服务器

I have a SQL Server instance that I've added a linked server to another SQL instance. The table I'm accessing on the linked server contains spatial types. When I try to query the table I receive an error:

我有一个SQL Server实例，我已经将链接服务器添加到另一个SQL实例。我在链接服务器*问的表包含空间类型。当我尝试查询表时，收到错误：

Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object.

在分布式查询中不允许使用CLR类型公开列的对象。请使用传递查询来访问远程对象。

If I use OPENQUERY with the same query I get another error:

如果我使用相同的查询OPENQUERY我得到另一个错误：

A severe error occurred on the current command. The results, if any, should be discarded.

当前命令发生严重错误。结果（如果有的话）应该被丢弃。

Is there any way to query tables that contain spatial types via linked servers?

有没有办法通过链接服务器查询包含空间类型的表？

3 个解决方案

#1

One way to work around this is to pass spatial data as NVARCHAR(MAX)

解决此问题的一种方法是将空间数据作为NVARCHAR（MAX）传递

select go=geometry::STGeomFromText(go,0)
from openquery([other\instance],
'select go=convert(nvarchar(max),go) from tempdb.dbo.geom')

note: go is a column name, short for geometry-object

注意：go是列名，是geometry-object的缩写

Or using the function instead of explicit cast

或者使用函数而不是显式强制转换

select go=geometry::STGeomFromText(go,0)
from openquery([other\instance],
'select go=go.STAsText() from tempdb.dbo.geom')

#2

I came across the same problem, but accepted solution wasn't an option in my case, due to many applications that couldn't be changed to expect a totally different query.

我遇到了同样的问题，但是在我的情况下，接受的解决方案不是一个选项，因为许多应用程序无法更改以期望完全不同的查询。

Instead, I think I found a way to cheat the system. On local server run:

相反，我认为我找到了欺骗系统的方法。在本地服务器上运行：

CREATE VIEW stage_table
AS
SELECT *
FROM OPENQUERY([REMOTESERVER],'SELECT * FROM [REMOTEDB].[SCHEMA].TARGET_TABLE');
GO
CREATE SYNONYM TARGET_TABLE FOR stage_table;
GO

Voila, you can now simply use

瞧，你现在可以简单地使用了

SELECT * FROM TARGET_TABLE;

Which is probably what your applications expect.

这可能是您的应用程序所期望的。

Tried the above scenario with local server: SQLEXPRESS 2008 R2, and remote server SQL EXPRESS 2014.

尝试使用本地服务器执行上述方案：SQLEXPRESS 2008 R2和远程服务器SQL EXPRESS 2014。

#3

I have another workaround. It doesn't apply to the OP's question since they were trying to select the spatial data. Even if you are not selecting the columns containing spatial data, you'll still get this error. So if you need to query such a table, and do not need to retrieve the spatial data, then you could create a view for the table (selecting only the columns you need, excluding the spatial data columns), then query against that view instead.

我有另一个解决方法。它不适用于OP的问题，因为他们试图选择空间数据。即使您没有选择包含空间数据的列，您仍会收到此错误。因此，如果您需要查询此类表，并且不需要检索空间数据，则可以为表创建一个视图（仅选择所需的列，排除空间数据列），然后针对该视图进行查询。

#1