ArcSDESQLExecute (arcpy) for stored proc

Question

anyone has an example to call stored proc from ArcSDESQLExecute (arcpy)? any help will be greatly appreciated

RonnieRichards · Answer

Adding SET NOCOUNT ON works for me to be able to call stored procedures using arcpy.ArcSDESQLExecute. This was last tested with version 10.5.1

PhilLarkin1 · Answer

This thread is old, but maybe this will help someone. I couldn't get this function to work. It kept returning True instead of rows. From the help doc we know what a Boolean return means. This isn't particularly helpful if you know a stored procedure returns rows. But at least it is something. ....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).‍‍‍‍‍‍‍‍‍‍‍‍‍ When i turned to pandas as an alternative I received a similar error message from sqlalchemy: File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\sqlalchemy\engine\result.py", line 1077, in _non_result "This result object does not return rows. " sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.‍‍‍‍‍‍‍‍‍‍‍‍ This led me to a stackoverflow comment that suggested using SET NO COUNT when working with stored procedures and sqlalchemy. I wondered if this might work with ArcSDESQLExecute. It didn't. But, it solved my problem with pandas. Here is an example if you want the result of a stored procedure and have hit a wall with this function. # Dependencies: ArcGIS PRO 1.4.1 (Python 3.5.4) # Currently using python instance: C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\python.exe # SQLAlchemy and pyodbc were added manually to arcgis pro interface import urllib import sqlalchemy as sa import pandas as pd #utilizing SET NOCOUNT sqlStatement = "SET NOCOUNT ON EXEC dbo.spPropCharaSelectByNBHD 312524" #connection string pointing to DSN created with ODBC Data Source Admin params = urllib.parse.quote_plus("DSN={yourDSNnameHERE};Trusted_Connection=yes") engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) #create pandas dataframe current_source = pd.read_sql_query(sqlStatement,engine)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

StephanieWidas1 · Answer

Did you find a way to execute stored procedures?