SQL Server spatial and linked servers

sql-server sql-server-2008 spatial linked-server
BryceH picture BryceH · Mar 11, 2011 · Viewed 20.7k times · Source

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

RichardTheKiwi picture RichardTheKiwi · Mar 11, 2011

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

Related questions

What's the best way to store co-ordinates (longitude/latitude, from Google Maps) in SQL Server?

I'm designing a table in SQL Server 2008 that will store a list of users and a Google Maps co-ordinate (longitude & latitude). Will I need two fields, or can it be done with 1? What's the best (or most common) data-type …

Read More
SQL Server 2008 Spatial: find a point in polygon

I am using SQL Server 2008 spatial data types. I have a table with all States (as polygons) as data type GEOMETRY. Now I want to check if a point's coordinates (latitudes, longitudes) as data type GEOGRAPHY, is inside that State …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More