SQL Server spatial and linked servers

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 que开发者_运维技巧ry the table I receive an error:

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

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

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?

One way to work around this is to pass spatial data as 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

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')

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.

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.