I am trying to call a stored procedure using arcpy.ArcSDESQLExecute to retrieve a single record. In MSSQL I can call the stored procedure and I get the expected value. When I use arcpy.ArcSDESQLExecute to execute the stored procedure I get a Boolean True value.
sdeConn.execute('EXEC dbo.usp_GetValue;') //Always returns true
Has anyone ever encountered this problem before.
Are you querying against versioned or unversioned tables? It appears the WHERE statement is against a text field, correct?
It is an unversioned feature class. I am using direct connect. The where statement does execute against a text field of Hash IDs.
Note: I have gotten around this by dropping Arcpy and using pymssql - it works and the overhead is pretty small. I would still be interested in hearing why I am being returned True (which is supposed to be returned in cases where no records are returned)
Can you get any query to work using arcpy.ArcSDESQLExecute or is it just this specific query that is having issues? What data type, specifically, is the hash ID field? Are any special characters used in the hashes, specifically backslashes?
arcpy.ArcSDESQLExecute() returns a boolean TRUE if a select statement was valid but no rows were returned. In your case, you're executing a procedure and I think it's just telling you the procedure ran successfully.
...for statements that do not return rows, it will return an indication of the success or failure of the statement (True for success; None for failure).
Maybe try reformatting your SQL into a select statement to trick it into expecting rows back.
Thanks for your help walking me through this. A little embarrassing, I rewrote the stub and it is working fine. This script isn't on git so unfortunately I cannot back track. It's curious I found the original post here, I now wonder what I/we were doing; it must have been a typo. Note, it wasn't a procedure but good call on that behaviour.
Very simple, as the sample goes..
import arcpy
print "Connecting to SQL Server.."
sqlConn = arcpy.ArcSDESQLExecute(r"\\gismsa\...\sde@iSphereBuoys.sde")
table_name = 'iSphereBuoys'
field_name = 'hashId'
sql = '''
SELECT {0} FROM {1} WHERE {0}='943906c71d6c93c7a2515b33d7a5be65'
'''.format(field_name, table_name)
sqlReturn = sqlConn.execute(sql)
print sqlReturn
#or
for i in sqlReturn:
print i
I also have this problem. I am executing a simply "SELECT column FROM table WHERE column='something'" and I am being returned True, even though the correct response is 2 rows of data. Anyone?