AnsweredAssumed Answered

Executing a Stored Procedure in MS SQL from arcpy.ArcSDESQLExecute

Question asked by rinelspitz on Jan 19, 2018
Latest reply on Jan 22, 2018 by rinelspitz

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?

Outcomes