topic Re: Execute SQL Server stored procedures from ArcGIS 10.1 in Data Management Questions

Execute SQL Server stored procedures from ArcGIS 10.1

IreneGM — Thu, 15 Nov 2012 20:52:07 GMT

Hi all,

I would like to know if it is possible to execute an already-defined stored procedure (in SQL Server) from ArcGIS 10.1 and use its output data as an input for my maps.

For example, the stored procedure receives some input parameters (date, status, type) and returns back a table with two columns, the first one with a polygon ID and the second is an integer that represents the number of points in the polygon matching the input parameters. So i would like to associate to my map, that column with the number of occurrences.

Is this possible? If so, how this would be done?

Please, could you provide some guidelines or link to a tutorial?

Thanks! 🙂

Re: Execute SQL Server stored procedures from ArcGIS 10.1

ArkadiuszMatoszka — Fri, 16 Nov 2012 06:49:29 GMT

I think best option would be creating some python tool.
There is tool that allows to execute SQL via SDE connection:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002z00000021000000.htm
if not, I'm sure there are some other libraries for python that allows calling SQLServer stored procedure in Python.
Incorporating such solution should be quite easy.
Regards
Arek

Re: Execute SQL Server stored procedures from ArcGIS 10.1

SunilPalkar — Thu, 20 Jun 2013 09:23:41 GMT

@arekmatoszka I tried above links but I think its not possible to execute stored procedure in ArcGIS 10.1

Does anyone tried above link ??

thanks..

I think best option would be creating some python tool.
There is tool that allows to execute SQL via SDE connection:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002z00000021000000.htm
if not, I'm sure there are some other libraries for python that allows calling SQLServer stored procedure in Python.
Incorporating such solution should be quite easy.
Regards
Arek

Re: Execute SQL Server stored procedures from ArcGIS 10.1

VinceAngelo — Thu, 20 Jun 2013 13:52:26 GMT

You can of course execute a SQL procedure in any number of ways, but the method
you describe doesn't match any of the existing models for accessing spatial data.
ArcGIS can read a table or view registered with an ArcSDE geodatabase, it can read
a table or view with a geometry column and a unique integer id through Query Layers,
and it can compute a point from x/y data in a table, but I don't know of any way that
you can join a stored procedure to a table with a geometry column using a SELECT
statement.

If you changed your procedure to populate a 1:1 table with the matching IDs, then
you could fashion a query or view to access the geometry column.

- V