How to call an SQL Server stored procedure from ArcMAP

[AhmadSALEH1]

Hi all,

I wounder if there is a method to call SQL Stored Procedures from the ArcMAP?

I can use python  "execute(sql_statement)" to do this but I am looking for other methods if there is ?

Thank you,

Ahmad 

[VinceAngelo]

Is there a reason you linked to the documentation of a retired release of ArcGIS?  Current documentation also references arcpy.ArcSDESQLExecute, which remains the only within-ArcPy method to execute ad-hoc SQL. Note that this function could be used to corrupt geodatabase metadata (with an injudicious DROP TABLE, for example), so extreme care should be used.  That said, I just executed a cascade of 1200+ SQL statements against a PostgreSQL database (thanks to Python dictionaries, string substitution, and nested looping), so the only reason to fall back to ODBC is if your return results are not supported by the API (the most dangerous being long (64-bit) integers and geometry columns).

- V

[TedKowal]

I don't believe there is a way within ArcGIS to do this. However, this is a good thing, as Vince noted, could be extremely dangerous.  So if you want to do this, you will have to resort to programming.  If you are doing this via programming, be ABSOLUTELY sure you know what you are doing.

My personal use in using stored procedures 99% of the time is only in a read only context to extract data to make outside joins upon.  The other 1% is strictly for updating non geometric attributes.

Have you looked at defining a "View".

[AhmadSALEH1]

Thanks Vince Angelo and Ted Kowal

Actually there is no  a reason for linking  documentation of a retired release of ArcGIS. I was trying to define some  Stored Procedures  in the SQL server and let the users call them from the Arcmap (mainly from the Query builder window)   , instead of writing very long query's ( 10 lines ) the user can pass the Procedure name - I believe this will reduce the errors  and the traffic over the network.

Ted, I am using stored procedures for read only purposes, in my case  I prefer SP than the normal database views since  they are more flexible than the views  ( the User can pass a parameter when he calls the SP).

So my only solution right now it to write python tool that can call these procedures to Arcmap!

Thanks for your help,

Ahmad

[VinceAngelo · Accepted solution]

Normal views can be accessed as Query Layers; stored procedures cannot.

Packets being what they are, I doubt you'd see a measurable difference in network traffic, even if the query only returns one row.

- V

[TedKowal]

I believe Vince Angelo answered this completely and Dale_Brooks-esristaff gave an excellent word warning!   Currently, I am using coding to access Stored Procedures, my case it is VB.Net wrapped up around various custom commands that I have created to work within ArcMap.  These, by nature, are very specific to your individual working environment.  Since I am not fully versed in the geometry stuff, I do not touch any esri specific fields but only use store procedures with the attributes... for example, in my shop we use LRS (Linear Referencing System). Most of our new roads are being constructed at the beginnings of the route  thereby constantly changing the beginning mile point.  So store procedures in my case allow me to trigger an event that changes all the assets that are collected in reference to Mileposts downstream of the change.

[DaleBrooks]

I am a big believer in use of stored procedures for their full capabilities, especially since the spatial columns in most common DBMS's have a SQL API to go with them (several are Esri ST_Geometry or Esri/DBMS Vendor implementations).  You can white holistic PL/SQL on spatial and non-spatial data tables (minding, of course, the documentation of the specific conditions under which SQL based programming is safe and supported).  See links below.

Very important regarding Stored Procedure development:

• Should be done only after fully reviewing and understanding Esri's documentation on SQL access to the database.  Start here:      SQL and enterprise geodatabases—Help | ArcGIS Desktop 

      (Also look in Esri's Proceedings for "SQL".  Watch for 2018 International and DC Developer Summit Proceedings, coming soon)  Recent Proceedings 

• Should be done by a highly experienced Esri geodatabase and DBMS developer