Menu
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?
973
Linked servers enable the SQL Server Database Engine and Azure SQL Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server.
The Azure SQL MI instance can be configured to access the tables on the SQL Server on the VM via a linked server on MI. To expose the tables and views on the remote non-SQL Server database engine, we use views in a SQL Server database to access the remote tables using 4 part names.
Linked Servers are a method by which a SQL Server can talk to another ODBC compliant database, such as another SQL Server instance or an Oracle database, with a direct T-SQL query. There are several important settings to know when setting up a Linked Server.
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') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
