ArcSDESQLExecute Error

[KushendraShah1]

I tried to join the three tables and select the records from the table in one of our arcsde database using python scripts, however I got a traceback error indicating "ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -37 ". I don't have any clue how to fix this error. Interestingly, when I join the two tables and run the query, it works well. Here is the snippet that gives error when run the query for three tables:

import arcpy
try:
sdeconn = r"Database Connections\Database.sde"
sql = "SELECT * \
FROM Table1 \
JOIN Table2 \
ON Table2.column1 = Table1.column2 \
JOIN Table3 \
ON Table3.column3 = Table1.column2 \
WHERE AgriLogger_Table1.column 2 = 3"
cnn = arcpy.ArcSDESQLExecute(sdeconn)
sqlresult = cnn.execute(sql)
for i in sqlresult:
print i
except Exception as err:
print unicode(err.message).encode("utf-8")
print err

[JoshuaBixby]

Do the tables you are querying have spatial columns?  If so, that is likely throwing the error because arcpy.ArcSDESQLExecute can't work with them.

[KushendraShah1]

Joshua- There is no spatial columns in any of those tables. It doesn't throw error when join and query two tables.

[JoshuaBixby]

Are your table names really "Table1," "Table 2," etc...?

A few comments:

• At some point, you abstract the information enough from reality (e.g., "Table1" instead of actual table name and "Column1" instead of actual column name) that it becomes difficult for people to provide meaningful feedback.
• It would be helpful to provide more specifics about columns involved?  "SELECT * " is not a good practice, either for developing SQL or troubleshooting it. 
  • arcpy.ArcSDESQLExecute doesn't like more than just spatial columns.  If you can provide the various data types of columns, it might become apparent if one of the columns is the issue.
• What is the SQL that works?

[KushendraShah1]

Here is the sql that works for two table:

import arcpy

try:
sdeconn = r"Database Connections\SDataGIS.sde"
sql = "SELECT * FROM TableLocations JOIN TableMetaData ON TableLocations.LocationID = TableMetaDataDetails.AgriLoggerID WHERE TableMetaDataDetails.AgriLoggerID = 78"
conn = arcpy.ArcSDESQLExecute(sdeconn)
sqlresult = conn.execute(sql)
for i in sqlresult:
print i
except Exception as err:

print err

# when I brought the 3rd table and run this query, it works.

[KushendraShah1]

# when I brought the 3rd table and run this query, it doesn't not work.

[JoshuaBixby]

According to the ArcSDE return codes:

SE_TABLE_NOEXIST (-37)

The specified DBMS table does not exist.

Are you sure the syntax with the 3rd table is correct?  Have you tried executing the SQL outside of ArcGIS to see if it returns an error?

[KushendraShah1]

I tried and run outside of ArcGIS and it works well. Actually my 3rd table was not correct(made typo). Also, I used the 3rd table for a feature class, it seems like ArcSDESQL doesn't work for spatial column. I got an error of "ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65". I know some of the columns has null values in them. It would be great if I can perform join in Spatial data not just tables and perform query to extract the information. Thank you so much for getting into this Joshua. I appreciated for the help.

[JoshuaBixby · Accepted solution]

Yes, arcpy.ArcSDESQLExecute was not designed to retrieve spatial data.  It isn't an issue of NULL or not NULL, it is the fact that one of your columns is an unsupported data type.

Since you are using an enterprise DBMS already, you should look into query layers (What is a query layer? ).  You can write SQL JOIN statements that include spatial columns with query layers. 

[KushendraShah1]

Thanks for the info Joshua.