SQL Server spatial and linked servers

Question

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:

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?

Answer 1

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