transfer of Microsoft SQL statement to a statement that can be run against our SDE database

[Anonymous User]

Hi,

we are migrating to a backend Microsoft SQL database and I have constructed a number of SQL statements like :

SELECT valvetype, count (VALVETYPE) FROM ssw_gis.valve
WHERE administrativearea= 'SST'
GROUP by VALVETYPE

The statement below gives me a count of valvetypes that have been migrated across from a legacy system to an eSRI solution so that we can balance both systems during the transformation.

As a stop gap before we directly send the data into our Microsoft tables we are creating a SDE database locally.

The question I have is there a way of copy and pasting the Microsoft SQL I have scripted that is going direct to the database into ARCMAP or ARC Catalog to generate the counts of items. If not then is there a way of remapping my Microsoft scripts to run direct in any of these ESRI Apps ?

[RexRobichaux2]

Hello Chris, 

 The only methods that I can think of that might work for you depending upon the nature of the data and your needs would be to create new SQL views in the database using our current select / sql queries. Once you create these views in SQL, they could be consumed in ArcGIS clients so long as they have the necessary prerequisites (spatial or non-spatial). For example, I used a similar query to create a non-spatial tabular SQL view which can be added in ArcMap and viewed:

Create view Acres (Acres, acrescount) as select Acres, COUNT(*) from dbo.CITYBND where DATA = 'Elm City' group by Acres;

The other option which might work would be to create definition queries within your existing tables / feature classes although this will vary depending on the nature of the SQL statements used. I hope this is helpful!

[Anonymous User]

cheers Rex,

I can see how this could work but still seems extra steps to take when all I am after is a simply count by statement with a group by clause.

Q> is there no way to call a piece of SQL from within the app to return this or run it within a defined query ?

[AlexanderBrown5]

Chris,

If I am following you correctly, you just want to be able to run the SQL within ArcGIS clients.  I would write a repeatable python script.

You can use this method:

ArcSDESQLExecute—ArcPy classes | ArcGIS Desktop 

~Alex

[Anonymous User]

Thanks Both.

with these two replies I think I can work with either the Python Script or the creation of the views with our SQL DBA