ArcGIS Pro and local SQL Express DB

[StuartMoore]

Hi,

i've searched everywhere (well everywhere i can think off) and i am struggling, back when i had ArcMap 10 you could create an local SQL express DB but i can't seem to find the same in ArcGIS Pro.

i've installed SQL Server Dev edition and can connect Pro to it, i can create a feautre class in the SQL DB, i can copy a feature class to the SQL DB but when add it into pro to update / create new features its added as a query layer and i cant edit it

i am guessing i've done it wrong some how....

Stu

[AZendel]

@George_Thompson , are there any updates to this? We rely on the 'Desktop Geodatabase' (formerly 'Personal ArcSDE' database) quite heavily. We are trying to ween our staff off of ArcMap since it won't be supported much longer. 

[George_Thompson]

I have not seen any updates on this topic. I would look towards using file geodatabases and / or SQL Server (Standard / Advanced) enterprise geodatabases.

You may want to log a case with support to see if they have any other information.

[AZendel]

We often have databases with 200K, sometimes 1M+ records. Geoprocessing is way too slow when doing joins --> select by attribute --> calculate field, even with proper indexes. SQL can do it in a couple of seconds (literally) with UPDATE ... INNER JOIN....WHERE. There are many other areas where SQL is much more efficient than geoprocessing (and sometimes the opposite is true). 

Beyond the need for Desktop Geodatabases, is ESRI going to continue to offer the Workgroup database? It seems like those can't be administered from Pro either. 

[JackYoder]

I was able to use ArcMap/ArcCatalog to create and SQL Express database and transfer my geodata from  my old personal MS access database.  I can connect to the new database in ArcGIS Pro and add the layers just fine.  The problem is that I can't edit them.  When I tried to edit a layer in the SQL Express database I get a red exclamation point next to the layer.  Hovering over it is says "The layers workspace is read only..."

I can't seem to find any documentation on this.  I'm not sure if I did something wrong creating the database or if somehow I need to tell ArcGIS Pro it is not read only or give ArcGIS write privilege. 

Any guidance would be helpful.  I can't really use the file or SQL Lite options.  My old MS Access personal Geodatabase had many MS Access forms and macros to manipulate non-geo data that is related.  The only work around I could think of was to use an SQL Express workgroup database and used linked tables in MS Access for the data manipulation.  I only have a personal use license, so I have no support or enterprise licensing. 

[George_Thompson]

Unless you made the SQL Express database a geodatabase (which would require the Workgroup license level) this is expected behavior: https://pro.arcgis.com/en/pro-app/3.3/help/data/databases/databases-and-arcgis.htm#ESRI_SECTION1_A18...

https://pro.arcgis.com/en/pro-app/3.3/help/data/databases/databases-and-arcgis.htm:
Relational databases store and organize highly structured data in tables that are composed of rows (or records) and columns (or fields). Tables are related to one another through key columns in each table.

This model of data storage provides a more linear but predictable model of data relationships.

You can connect to a relational database to do the following:

• Visualize and analyze existing data.
• Filter the data.
• Manage existing data.
• Add data from other sources.
• Share database content by publishing it to a stand-alone or federated ArcGIS Server site. To allow users to edit the data, publish the data as a web feature layer or feature service.

Hope that helps!

[JackYoder]

I'm sure it is a workgroup database that I created with ArcMap/ArcCatalog, because all of the layers display correctly in ArcGIS Pro.  I can connect to it and manipulate the layers stored in it just fine except for the fact that I can't edit them.

I only have a personal use license for ArcMap and ArcGIS Pro.

[George_Thompson]

If you connect in SSMS and look at the database, do you see any "DBO / SDE" repository tables?

Specifically, "sde.version" table?

Example of the table:

You can load data into a SQL non-geodatabase and it will display and work with Pro with no issues. See my example below:

[JackYoder]

No, it looks like I did something wrong creating the geodatabase in ArcMap/ArcCatelog.  Can you point me to the detailed instructions for that?  I only see my own tables in SSMS attached pictures.  

I was under the impression that I could use ArcMap/Catalog to create a workgroup SQL Express database that can be accessed by ArcGIS Pro.  Is that not correct?

[JackYoder]

After beating this dead horse long enough I got tired.  I did find an ODBC driver for SQLite so I think I should be able to use MS Access as a front end for it as I'm doing with SQL Server Express.  My web site connects to SQL Server Express for data, but I think I can use SQLite for that as well.  

So, I abandoning the idea of converting from my personal MS Access database to SQL Server Express (even though I've finished the conversion).  Since I have not been able to resolve the issue of editing layers using ArcGIS Pro with the SQL Server Express database, I'm going to redo everything using SQLite.

I was able to create and SQLite database, copy a layer to it from SQL Server Express. and then edit that layer in ArcGIS Pro.  

[AZendel]

@JackYoder , I'm in similar boat where we need MS Access front end to a geodatabase. I've tried using a SQLite-based geodatabase with the free and open source ODBC SQLite driver. Unfortunately, I ran into at least two deal-breaking / show-stopping snags.

1) Access sees all SQLite text/string fields as long text, even is the field is limited to 3 characters. Access won't join on long text fields. That's the big problem for me. Access also won't sort on long text in table view (but I'm pretty certain it can sort in a query).

2) you cannot update any rows that have geometry, even when editing a field that has nothing to do with geometry (e.g., an integer field or date field). This is speculation: many SQL database treat an update as deleting the original row and inserting a new row with the updated value. I got errors stating that the geometry libraries couldn't be found to insert geometry and update the spatial index. Google led me to trying to install some Spatialite libraries, but I still couldn't get it to work. I suspect that geometry support needs to be baked into the ODBC driver, but I couldn't find one. 

The loss of "personal/desktop" and "workgroup" SQL Express database is dang frustrating, ESRI. That's especially true after changing the Pro licensing and not allowing floating licenses for extensions. That financial squeeze has made us uninstall ArcGIS desktop products from several co-workers' computers. We will probably be providing them with QGIS instead.