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?
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.
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.
Those are the first questions that come to mind.
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 <firstname.lastname@example.org>
Betreff: Re: - Re: Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute
Re: Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute
reply from Joshua Bixby <https://community.esri.com/people/bixb0012?et=watches.email.thread> in Python - View the full discussion <https://community.esri.com/message/743823-re-executing-a-stored-procedure-in-ms-sql-from-arcpyarcsdesqlexecute?commentID=743823&et=watches.email.thread#comment-743823>