topic Stored Procedure call in esri python in Python Questions

Stored Procedure call in esri python

GIS_Solutions — Mon, 15 May 2017 13:43:05 GMT

Hello,

I want to run a stored procedure prepared in the mssql Over the ESRI python

I wrote a code like this, but I get an error on the last line

# Create connection
arcpy.ArcSDESQLExecute(server='172.16.200.16', instance='sde:sqlserver:172.16.200.16', database='BYS', user='cbssa',password='Adm18712')
# Execute stored procedure
sql = "EXEC dbo.MyStoredProcedure @id = uSP_DB_IslemYap_CELIKVANA"
db_connection.execute(sql)

Re: Stored Procedure call in esri python

KevinDunlop — Sun, 12 Dec 2021 07:36:01 GMT

You did not assign the db_connection. Try

# Create connection
db_connection = arcpy.ArcSDESQLExecute(server='172.16.200.16', instance='sde:sqlserver:172.16.200.16', database='BYS', user='cbssa',password='Adm18712')
# Execute stored procedure
sql = "EXEC dbo.MyStoredProcedure @id = uSP_DB_IslemYap_CELIKVANA"
db_connection.execute(sql)‍‍‍‍‍

Re: Stored Procedure call in esri python

GIS_Solutions — Tue, 16 May 2017 07:47:26 GMT

Thank you Kevin for reply

I made the change and this time I get the error.

Re: Stored Procedure call in esri python

sapnas — Thu, 18 May 2017 21:14:45 GMT

Create a sde file from you database connection string and you can call stored procedure as follows:

sde_Conn = arcpy.ArcSDESQLExecute(DBConnection.sde)

For Oracle
sde_Conn.execute("call dbo.MyStoredProcedure(uSP_DB_IslemYap_CELIKVANA)")

I found this information in another discussion but unfortunately I could not locate the link.

For SQL Server

sde_Conn.execute("exec dbo.MyStoredProcedure(uSP_DB_IslemYap_CELIKVANA)")

Re: Stored Procedure call in esri python

StephanieWidas1 — Wed, 08 Nov 2017 18:42:34 GMT

We have been running stored procedures using the method shown above, but it is no longer working since upgrading SQL from 2008 to 2014. We now get an error stating that “The metadata could not be determined because statement...uses a temporary table."

Any suggestions or a work-around to get our existing stored procedures to run from python scripts? Is there a better approach rather than using arcpy.ArcSDESQLExecute to run a stored procedure?

Thanks in advance,

Stephanie

Re: Stored Procedure call in esri python

DavidBlanchard — Wed, 15 Nov 2017 21:02:13 GMT

The way to call stored procedures from ArcPy has not changed with SQL Server 2014. I work on a project that runs the stored procedure using

sde_Conn.execute("exec dbo.MyStoredProcedure(...))‍

in SQL Server 2014.

The error you are getting is due to a difference in the way SQL Server fetches metadata starting with SQL Server 2012. A quick web search reveals several possible solution.

You may be able to resolve this issue by replacing the temporary table with a Common Table Expression or a Table Variable.

There could also be issues with your returned dataset (if you're returning anything). I know the WITH RESULTS SETS syntax allows you to explicitly set the format, but I'm not particularly familiar with it.