Rx_
Nothing In Moderation
- Local time
- Today, 05:42
- Joined
- Oct 22, 2009
- Messages
- 2,803
ESRI GIS Oracle ODBC - SQL Server Linked Server Invalid metadata for column "SHAPE"
Is anyone using ESRI GIS on Oracle and then using SQL Server to consume Oracle views with SQL Server Linked Servers via ODBC?
This problem / solution has not been validated until the GIS group responds. Does this look like a possible cause / solution? Any other possible reasons would be welcome.
On this same SQL Server Linked Server to Oracle, I am able to read many views and tables that are pure data (not geodata). These pure data views and tables do not involve the SHAPE to determine geograpical areas. Those tables and views named to indicate geopatial data filters have the exact same "SHAPE" error. SHAPE is not a column (field) shown in the view.
Any query for views / tables based on geodata gets this error. Please note that SHAPE is not one of the columns (fields) viewable or exposed from the table:
The OLEDB provider "OraOLEDB.Oracle" for linked server "<name of server>" supplied invalid metadata for the column "SHAPE". The data type is not supported.
ESRI for Oracle, Shared Data:
http://support.esri.com/em/knowledgebase/techarticles/detail/35658
Cause : The Oracle parse error occurs because the shape.area or shape.len attribute is not fully qualified with its table name or a table alias in the SQL statement being executed in the database.
Without fully qualifying the attribute, the shape.area or shape.len attributes are not valid attributes for the table being queried. The area and len attributes are properties of the st_geometry type and therefore must be fully qualified with the table alias when referenced within the SQL statement.
Solution or Workaround
The solution to the error is to fully qualify the shape.area or shape.len attribute in the definition queries 'where' clause.
For example when adding a definition query for a layer named 'water_bodies' in ArcMap, an Oracle parse error with the following syntax is encountered:
"NATION" = 52 AND "SHAPE.AREA" >= .000010
By fully qualifying the attribute with the table name, no error is encountered.
"NATION" = 52 AND "WATER_BODIES.SHAPE.AREA" >= .000010
My further reading indicates that the SHAPE is an ESRI Oracle System file with rights only available to the system user. My ODBC rights do not provide rights access to system files. So, the Error as translated by ODBC is shown above.
Is anyone using ESRI GIS on Oracle and then using SQL Server to consume Oracle views with SQL Server Linked Servers via ODBC?
This problem / solution has not been validated until the GIS group responds. Does this look like a possible cause / solution? Any other possible reasons would be welcome.
On this same SQL Server Linked Server to Oracle, I am able to read many views and tables that are pure data (not geodata). These pure data views and tables do not involve the SHAPE to determine geograpical areas. Those tables and views named to indicate geopatial data filters have the exact same "SHAPE" error. SHAPE is not a column (field) shown in the view.
Any query for views / tables based on geodata gets this error. Please note that SHAPE is not one of the columns (fields) viewable or exposed from the table:
The OLEDB provider "OraOLEDB.Oracle" for linked server "<name of server>" supplied invalid metadata for the column "SHAPE". The data type is not supported.
ESRI for Oracle, Shared Data:
http://support.esri.com/em/knowledgebase/techarticles/detail/35658
Cause : The Oracle parse error occurs because the shape.area or shape.len attribute is not fully qualified with its table name or a table alias in the SQL statement being executed in the database.
Without fully qualifying the attribute, the shape.area or shape.len attributes are not valid attributes for the table being queried. The area and len attributes are properties of the st_geometry type and therefore must be fully qualified with the table alias when referenced within the SQL statement.
Solution or Workaround
The solution to the error is to fully qualify the shape.area or shape.len attribute in the definition queries 'where' clause.
For example when adding a definition query for a layer named 'water_bodies' in ArcMap, an Oracle parse error with the following syntax is encountered:
"NATION" = 52 AND "SHAPE.AREA" >= .000010
By fully qualifying the attribute with the table name, no error is encountered.
"NATION" = 52 AND "WATER_BODIES.SHAPE.AREA" >= .000010
My further reading indicates that the SHAPE is an ESRI Oracle System file with rights only available to the system user. My ODBC rights do not provide rights access to system files. So, the Error as translated by ODBC is shown above.
