topic How to execute a SQL Server stored procedure with ArcPy in Python Questions

How to execute a SQL Server stored procedure with ArcPy

JoseSanchez — Wed, 02 Jan 2019 16:11:56 GMT

Hello everyone,

I need to execute a Ms SQL Server stored procedure using Arcpy. Any source code samples that will hep me.

Thanks

Re: How to execute a SQL Server stored procedure with ArcPy

JoshuaBixby — Wed, 02 Jan 2019 19:54:21 GMT

ArcSDESQLExecute—ArcPy classes | ArcGIS Desktop may work, it depends on a couple of factors.

Why do you have to execute the stored procedure in "ArcPy?" Why not just connect to it using a Python wrapper for SQL Server?

Re: How to execute a SQL Server stored procedure with ArcPy

JoseSanchez — Wed, 02 Jan 2019 20:59:54 GMT

I was looking for an option to run a Ms SQL Update statement because updating records using arcpy.da.UpdateCursor is too slow.

The first option was to add the SQL code in a stored procedure and run the stored procedure.

The second option, I just copy/pasted the code sample (http://pro.arcgis.com/en/pro-app/arcpy/classes/arcsdesqlexecute.htm) , added an Update SQL Statement to variable "sql_statement" , ran the code and it worked.

sql_statement = " UPDATE [owner].[Property] " \

"SET [FOLIO] = APP.TCIS_GIS.FOLIO " \

……..

sql_statement_list = sql_statement.split(";")

egdb_return = egdb_conn.execute(sql)

Thanks

Re: How to execute a SQL Server stored procedure with ArcPy

JoshuaBixby — Wed, 02 Jan 2019 23:53:39 GMT

Glad you could get something working. Just a note of caution, ArcSDESQLExecute doesn't recognize versions, so you will be making updates to base tables unless your SQL is using versioned views.

Re: How to execute a SQL Server stored procedure with ArcPy

JoseSanchez — Thu, 03 Jan 2019 12:48:28 GMT

In this case layers are not versioned, so ArcSDESQLExecute runs fine.