arcpy.ArcSDESQLExecute returning Boolean for EXEC SQL Statement?

Question

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.

TomGiles · Answer

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

blake.terhune · Answer

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.

bixb0012 · Answer

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?