Community
Select to view content in your preferred language

Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute

2349
3
01-19-2018 09:40 AM
PiusHauenstein
by
Occasional Contributor
‎01-19-2018 09:40 AM

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?

0 Kudos
3 Replies
ChrisPedrezuela
by
Frequent Contributor
‎01-21-2018 07:26 PM

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.

0 Kudos
JoshuaBixby
by MVP Esteemed Contributor
MVP Esteemed Contributor
‎01-22-2018 07:25 AM

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.

  1. What version of ArcGIS Desktop, or is it ArcGIS Pro you are using?
  2. What version is the enterprise geodatabase?
  3. What DBMS and version?  (You mention MS SQL Server but no version)
  4. What version of local DBMS clients are installed?  (It appears SQL Server Native Client 11.0, any others?)
  5. If ArcGIS Desktop/ArcMap:
    1. Is Background Processing enabled or disabled?
    2. Is 64-bit Background geoprocessing installed?
  6. Does your SP work against registered tables, unregistered, or both?
  7. If working with versioned data, are you using the versioned views?
  8. Have you tried running the SQL within a transaction using ArcSDESQLExecute?

Those are the first questions that come to mind.

PiusHauenstein
by
Occasional Contributor
‎01-22-2018 07:45 AM

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 <pius.hauenstein@alumni.ethz.ch>

Betreff: Re: - Re: Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute

<https://community.esri.com/?et=watches.email.thread> GeoNet

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>

0 Kudos