topic Re: ArcSDESQLExecute (arcpy) for stored proc in Data Management Questions

ArcSDESQLExecute (arcpy) for stored proc

sapnas — Fri, 17 Feb 2017 17:08:37 GMT

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

Re: ArcSDESQLExecute (arcpy) for stored proc

George_Thompson — Fri, 17 Feb 2017 17:13:13 GMT

Here is an example from the help docs: ArcSDESQLExecute—Help | ArcGIS Desktop

https://community.esri.com/groups/geodatabase?sr=search&searchId=541e9567-7aec-43e8-9fe8-a92e954e1c4c&searchIndex=0‌ https://community.esri.com/community/developers/gis-developers/python?sr=search&searchId=cfbdcbe2-5f0d-436e-b26a-4f01ca453aeb&searchIndex=0‌

Re: ArcSDESQLExecute (arcpy) for stored proc

sapnas — Fri, 17 Feb 2017 18:08:54 GMT

That is an example for simple sql statements not stored proc which will not work in my case.

Re: ArcSDESQLExecute (arcpy) for stored proc

StephanieWidas1 — Wed, 08 Nov 2017 17:06:01 GMT

Did you find a way to execute stored procedures?

Re: ArcSDESQLExecute (arcpy) for stored proc

PhilLarkin1 — Sat, 11 Dec 2021 21:20:39 GMT

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

Re: ArcSDESQLExecute (arcpy) for stored proc

RonnieRichards — Thu, 05 Apr 2018 00:16:12 GMT

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