0

I'm on server1, it has a lot of dbs. I need to be able to query (select) geography type from different dbs specified. This works fine

SELECT GeoField=geography::STGeomFromText(GeoField,4326)
FROM 
(SELECT convert(nvarchar(max),GeoField) as GeoField from [server1].dbA.dbo.MyTable) as t

Now I need to query from server1, servers2 db. server2 is linked on server1. I can use this query which works fine

SELECT GeoField=geography::STGeomFromText(GeoField,4326)
FROM OPENQUERY([server2],
'SELECT GeoField=convert(nvarchar(max),GeoField) from dbB.dbo.MyTable')

All queries are run from server1. Constraint: I can't add server1 as a linked server on server1.

Question: how can I use one query to query both my current server (server1) and linked server (server2) by simply changing servername and dbname, and not use two different queries specified above?

Eric Klaus
  • 923
  • 1
  • 9
  • 24
  • Have you tried `SELECT GeoField=geography::STGeomFromText(GeoField,4326) FROM (SELECT convert(nvarchar(max),GeoField) as GeoField from server2.dbB.dbo.MyTable'`? – Ben Thul Dec 16 '20 at 05:58
  • Sure thing, it says geography type cna't be queried that way – Eric Klaus Dec 16 '20 at 14:07
  • Weird. What is the error message? – Ben Thul Dec 16 '20 at 21:22
  • Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object – Eric Klaus Dec 18 '20 at 00:51
  • Ah. Interesting. Try selecting the remote nvarchar column into a local temp table and then so the conversation to geography from there. Out of curiosity, what days type is GeoField natively? Doing two conversions is weird to me. – Ben Thul Dec 18 '20 at 02:53

0 Answers0