Executing Stored Procedure from Python

StephanieWidas — Mon, 28 Nov 2016 19:10:41 GMT

Greetings!

I am trying to run a stored procedure (with a parameter) from a python script. The stored procedure joins some tables and inserts a record from the joined tables into another table. The stored procedure works fine when I execute it from within Microsoft SQL Server Management Studio. When I run the stored procedure from Python, it inserts the record... but it inserts it 3 times!

Here is the snippet of code from Python:

import arcpy

db_connection = arcpy.ArcSDESQLExecute ("path to the SQL connection file")

sql = "EXEC dbo.MyStoredProcedure @id = 1"

db_connection.execute(sql)

Additional info:

1. The table and stored procedure are not in with our SDE. It's in a separate "non-GIS" database.

2. I'm using Python because I'm taking the record that is generated in the stored procedure and will be using it in geoprocessing tasks on down the line.

3. SQL Server 2008 R2

4. ArcGIS 10.3.1

Any ideas as to why it is as if the stored procedure is being executed 3 times when called from Python? Is there a better way to execute the stored procedure?

Thank you,

Stephanie

topic Executing Stored Procedure from Python in Developers Questions

Executing Stored Procedure from Python