ArcSDESQLExecute (arcpy) for stored proc

2148
5
02-17-2017 09:08 AM
Highlighted
by
Occasional Contributor III

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

Tags (1)
0 Kudos
5 Replies
Highlighted
by
Occasional Contributor III

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

0 Kudos
Highlighted
New Contributor II

Did you find a way to execute stored procedures?

0 Kudos
Highlighted
Occasional Contributor III

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
Highlighted
Regular Contributor

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