Enable Openquery for SQL views in Geodatabases

[JeffTimm]

If I want to run a SQL query to create a view from another database on another server I create a linked server.  I then can query non spatial data and join into my geodatabase.

The problem lies in that a standard query cannot bring CLR data type across the linked server connection.  In order to do that you need to use something along the lines of this.

Create view [cio].[vw_Dataset]
as
SELECT *

FROM OPENQUERY([gpcsqlc1], 'SELECT

 [OBJECTID]
,[District]
,[Name]
,[Headquarters]
,[Address]
,[City]
,[Zip]
,[Phone]
,[DateCreated]
,[Creator]
,[DateModified]
,[Editor]
,[GlobalID]
,[Shape] FROM [GIS_EDITS].[sde].[ADMINISTRATIVEDISTRICTS_evw]' )

Openquery apparently isn't supported in geodatabases.  This query works fine in SQL Management Studio but breaks when it is registered with the geodatabase.

#query result

[MarcoBoeringa]

Don't know if it is of any use to you, but I recently explored the options to create spatial views using pyodbc and the PostgreSQL ODBC drivers. This seemed to work quite nicely, and I could bring the data back into ArcGIS Pro using Query Layers. You do need to mind data columns you add to the view. Some column types are not supported by ArcGIS. E.g. PostgreSQL's hstore column type for key-value storage cannot be part of the final result of columns in the view (you can use it deeper down in the SQL, just not in the final columns exposed by the view).

pyodbc is part of the default Python install of ArcGIS Pro. I only needed to install the PostgreSQL ODBC drivers. pyodbc also supports other databases besides PostgreSQL:

Home · mkleehammer/pyodbc Wiki · GitHub