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?

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

Solution 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.

Solution 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.

Comments

[Aymeric Gaurat-Apelli]

for geography, use: geography::STGeomFromText(go, 4326)

[AAsk]

I encountered 'Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object ': the hint from pkExec resolved this problem.

[Triynko]

Just be clear. The exact transform necessary is as follows. If a query of the form select * from [remoteservername].[remotedatabasename].[schemaname].[tablen‌​ame] is failing, then simply replace it with select * from OPENQUERY([remoteservername], 'select * from [remotedatabasename].[schemaname].[tablename]'). You basically have to move the remote server name (i.e. not its domain name or anything like that... but the arbitrary name you linked it as) outside the table identifier and pass it as a parameter to openquery.

[pkExec]

@Triynko My answer covers even the case where the form of the query is "SELECT * FROM table_name". If you use what you mention in your comment, then for spatial datatypes the query will fail (as per AAsk 's comment).

[jumxozizi]

The view and synonym are indeed optional.

[drinky]

This does work. but be aware if you are deploying using octopus it will still throw an error. -Calamari.exe : Use-DatabaseReleaseArtifact : Applying update script failed: Objects exposing columns with CLR types are not allowed -