AnsweredAssumed Answered

ArcSDESQLExecute compatible with OPENQUERY?

Question asked by PSLarkin_SpokaneCounty on Nov 27, 2018
Latest reply on Nov 27, 2018 by PSLarkin_SpokaneCounty

Summary: Running ArcSDESQLExecute fails when using OPENQUERY.

 

Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.(18456)

 

System:   SQL Server 2008r2 (Enterprise Database - "DB1") and 2016 SP2 (Non-Enterprise Database connected with                 OPENQUERY - "DB2")

                Driver: ODBC Driver 13 for SQL Server

                Python: Pro 2.2.2 instance (3.6.5)

                Connection Files Created with Catalog 10.3.1

 

Details: 

Executing a simple query with ArcSDESQLExecute is successful. When OPENQUERY is used it appears credentials are not passed to the joined server/database. I've tested this with several users that are added as Logins to the Servers and Databases. This query runs properly in SQL Server Management Studio.

 

Other SQL Log Errors: 

  • There is already an object named '##SDE_8868_184399_DB1' in the database.
  • Invalid object name 'DB1.dbo.SDE_branches'. (208) 
  • Statement(s) could not be prepared. (8180)
  • Invalid object name 'DB1.PSLARKIN.SDE_logfiles'. (208)    <- Will reference my user name regardless of which OS user is set in the SDE connection file.

 

Query: 

SELECT gis_ACO_NUM , PID_NUM FROM 
     (SELECT
               LTRIM(RTRIM(ACO_NUM)) ACO_NUM
               ,LTRIM(RTRIM(PID_NUM)) PID_NUM
          FROM DB1.DB1SCHEMA.SEGPOINTS
          WHERE ( SUBSTRING(PID_NUM,6,1) = '.' AND LEN(PID_NUM) = 10 ) OR ( SUBSTRING(PID_NUM,6,1) = '.' AND LEN(PID_NUM) = 11 AND SUBSTRING(PID_NUM,11,1) NOT LIKE '%[^a-zA-Z]%' )    
         
          UNION

          SELECT
               LTRIM(RTRIM(ACO_NUM)) ACO_NUM
               ,LTRIM(RTRIM(PID_NUM)) PID_NUM
          FROM DB1.DB1SCHEMA.SEGHISTORYPOINT
          WHERE ( SUBSTRING(PID_NUM,6,1) = '.' AND LEN(PID_NUM) = 10 ) OR ( SUBSTRING(PID_NUM,6,1) = '.' AND LEN(PID_NUM) = 11 AND SUBSTRING(PID_NUM,11,1) NOT LIKE '%[^a-zA-Z]%' )    
     ) AS segPoints
     --section above the join will run correctly with ArcSDESQLExecute. Section below will not.
     LEFT JOIN
               (SELECT DISTINCT
                         CASE WHEN SUBSTRING(seg_merge_nbr,3,1) = '-' AND SUBSTRING(seg_merge_nbr,1,2) IN ('97','98','99') THEN '19' + REPLACE(seg_merge_nbr,'-','')
                              WHEN SUBSTRING(seg_merge_nbr,3,1) = '-' AND SUBSTRING(seg_merge_nbr,1,2) IN ('00') THEN '20' + REPLACE(seg_merge_nbr,'-','')
                              ELSE seg_merge_nbr
                              END gis_ACO_NUM
                         ,parent_parcel
                      FROM OPENQUERY(DB2Server,'SELECT seg_merge_nbr,parent_parcel,seg_status_cd FROM DB2Server.DB2.DB2Schema.seg_merge')
                      where seg_status_cd = 'DONE'
               ) AS asc_seg
               ON ACO_NUM = gis_ACO_NUM AND PID_NUM = parent_parcel
               WHERE gis_ACO_NUM is not null

Outcomes