Enable Openquery for SQL views in Geodatabases

Idea created by jtimm on Jun 15, 2018
    New
    Score60
    • Shelley.Schulte
    • claire.inbody
    • jtimm
    • keamarie
    • casey.dunn@Nebraska.gov_nebraska
    • John.Watermolen@Nebraska.gov_nebraska

    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