Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute

Question

I have a Store Procedure in MS SQL Server, which does his work correctly as long as I start (exec) it directly in the database (with sqlcmd or MS  SQL Server Managment Studio). Because this SP is embedded in a bigger dataprocessing job with Python and arcpy I want to start it with arcpy.ArcSDESQLExecute and .execute(). Now I get an error-Message:

"AttributeError: ArcSDESQLExecute: SreamExecute ArcSDE Extended error 24000 [Microsoft][SQL Server Native Client 11.0]Invalid cursor state"

In my SP there are many DELETE, SELECT, INSERT statements. When I ommit them, I get no error message an I get the my excepted return-code (which I have coded in the SP depending of the result of the Procedure).

When I use a SP with only a simple SELECT statement, I get back a Python list with the result of the query.

How does I have to formulate my SP, to have done the work with SELECT etc. and get back my return code?

PiusHauenstein · Answer

Hi I found the reason: 1. I had to add the «SET NOCOUNT ON» to the Stored Procedure 2. The Transaction Counts have to be balanced («Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements»). Therefore I had to reorganize the procdure (BEGIN TRY, BEGIN TRANSACTION, END, COMMIT TRANSACTION, BEGIN CATCH …) Nevertheless, Thanks for your help. Von: Joshua Bixby Gesendet: Montag, 22. Januar 2018 16:26 An: Pius Hauenstein Betreff: Re: - Re: Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute GeoNet Re: Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute reply from Joshua Bixby in Python - View the full discussion

bixb0012 · Answer

In case you haven't read it:  Executing SQL using an EGDB connection—Help | ArcGIS Desktop .  There are lots of caveats when using ArcSDESQLExecute.

It would help to post some of the code, Python and/or SQL.  There are numerous variables in play when using ArcSDESQLExecute and we haven't been given information on many.

* What version of ArcGIS Desktop, or is it ArcGIS Pro you are using?
* What version is the enterprise geodatabase?
* What DBMS and version?  (You mention MS SQL Server but no version)
* What version of local DBMS clients are installed?  (It appears SQL Server Native Client 11.0, any others?)
* If ArcGIS Desktop/ArcMap:* Is Background Processing enabled or disabled?
* Is 64-bit Background geoprocessing installed?

* Does your SP work against registered tables, unregistered, or both?
* If working with versioned data, are you using the versioned views?
* Have you tried running the SQL within a transaction using ArcSDESQLExecute?

Those are the first questions that come to mind.

ChrisPedrezuela · Answer

possibly try pypyodbc instead of arcpy to implement SQL transactions, this one has native SQL drivers compared to arcpy.ArcSDESQLExecute(). Possibly will solve your issue.