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)
Thank you Kevin for reply
I made the change and this time I get the error.
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)")
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
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.
You did not assign the db_connection. Try