Stored Procedure call in esri python

1814
5
05-15-2017 06:43 AM
GIS_Solutions
Occasional Contributor

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)

5 Replies
KevinDunlop
Occasional Contributor III

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)
GIS_Solutions
Occasional Contributor

Thank you Kevin for reply

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

0 Kudos
sapnas
by
Occasional Contributor III

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

StephanieWidas1
New Contributor II

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

0 Kudos
DavidBlanchard
Esri Contributor

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.

0 Kudos