ArcSDESQLExecute Error

2525
9
Jump to solution
08-17-2016 09:46 AM
KushendraShah1
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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. 

View solution in original post

0 Kudos
9 Replies
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos
KushendraShah1
New Contributor III

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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?
0 Kudos
KushendraShah1
New Contributor III

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.

0 Kudos
KushendraShah1
New Contributor III

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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?

0 Kudos
KushendraShah1
New Contributor III

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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. 

View solution in original post

0 Kudos
KushendraShah1
New Contributor III

Thanks for the info Joshua. 

0 Kudos