Linked Server — Data type is not supported

One day I was trying to query Oracle data through a SQL Server Linked Server and received the following error:

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "CEGIS" supplied invalid metadata for column "SHAPE".
The data type is not supported.

 

The error shows it cannot support the column name SHAPE, but the query is not even selecting that column!  In case you are wondering, SHAPE has a datatype of ST_Geometry which allows you to store spatial data such as location of a landmark, a street or a parcel of land.

ls1

 

A way to get around this mystery is to use OPENQUERY.  Using this syntax, the query comes back correctly.

ls2

 

Now I need to figure out why the original query was trying to use the column SHAPE when it was not mentioned in the query, but that will be another post.

For more information on Linked Servers, the following links are very helpful:

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-2017

and

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017

For more information on how I created the Linked Server to Oracle, please read my blog post here: Linked Server that connects to Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s