topic Re: ArcGIS Pro and local SQL Express DB in Geodatabase Questions

ArcGIS Pro and local SQL Express DB

StuartMoore — Thu, 19 Sep 2019 06:20:27 GMT

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

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Thu, 19 Sep 2019 10:40:52 GMT

The old version of the SQL Express geodatabase was referred to a personal or workgroup geodatabase. See more details here: ArcSDE for SQL Server Express limitations?

What you are experiencing is expected with SQL Server Dev (or any "full" version of SQL Server). You have to create/enable an enterprise geodatabase to have the edit functionality from ArcGIS Pro; Databases and ArcGIS—Help | ArcGIS Desktop

Here are some other GeoNet post related to ArcGIS Pro and SQL Server Express:

Manage Sql Server Express

Using ArcPro can you create a geodatabase in SQL Express Server?

Let me know if you have any other questions.

Re: ArcGIS Pro and local SQL Express DB

ZacharyHart — Thu, 08 Oct 2020 13:30:42 GMT

All I'm seeing from multiple threads is that there is absolutely no way to create a GDB in SQL Express with Pro.

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Fri, 09 Oct 2020 12:02:18 GMT

As far as I know that is still correct. ArcGIS Pro only supports file and enterprise geodatabases.

Client and geodatabase compatibility—ArcGIS Pro | Documentation

ArcGIS Pro (supports connecting to file and enterprise geodatabases)
ArcGIS Server services (support connecting to file, workgroup, and enterprise geodatabases)

Re: ArcGIS Pro and local SQL Express DB

ZacharyHart — Fri, 09 Oct 2020 12:11:27 GMT

That's not quite correct and I'm sure this is a source of much confusion. You can still create DBs in SQL express (desktop or workgroup edition DB server) wirh ArcMap and then work with them just fine in Pro.

You cannot CREATE those DBs in Pro, however.

I don't understand how Pro will be a full replacement for desktop with major functionality like this still missing.

I don't understand how after years of users asking the same/similar question that Esri cannot clarify the roadmap here.

Should we start planning that 2way replica environments will no longer be possible without a full ArcGIS Server Standard license for both participating DB instances??

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Fri, 09 Oct 2020 12:25:45 GMT

You are correct on the creation of SQL Express DB's using ArcMap. I was also able to find this FAQ: Is it possible to create a workgroup geodatabase in ArcGIS Pro?

I would highly recommend logging an enhancement with technical support, if not already done.

Re: ArcGIS Pro and local SQL Express DB

ZacharyHart — Tue, 13 Oct 2020 19:16:42 GMT

It seems to be discussed in this idea submission here.

However, this is a very broad topic and a huge thread of comments...I have no idea of knowing what enhancements are on track towards implementation.

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Tue, 13 Oct 2020 19:36:31 GMT

I did provide some feedback internally to the Geodatabase team based on this thread. I think that they are working on some documentation for the next release. I do not have any other information outside of that.

My opinion - I can understand the use cases for SQL Server express DB's but find that the limitations they impose (from the MS side more than Esri) creates issues overall with performance and size limits.

Nana Dei

Re: ArcGIS Pro and local SQL Express DB

ZacharyHart — Mon, 26 Oct 2020 13:01:36 GMT

Thank-you.

Performance issues aside, Desktop Database Servers (via SQL Express) are instrumental to distributed data workflows. It's important to remember that there are still places in the world where connectivity is sparse and bandwidth is meager such that direct DB connections (via WAN/VPN) or FS editing are simply not feasible. However, the replicated data model is time-proven and can also be enhanced with version management. Not being able to work with database server instances (using SQL Express) fundamentally cuts-out the 2-way replica option without having an ArcGIS Server license, and given what we are seeing here it's likely/possible that ArcGIS Workgroup is on the chopping block as well!

Re: ArcGIS Pro and local SQL Express DB

AZendel — Wed, 11 May 2022 16:49:42 GMT

@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.

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Wed, 11 May 2022 17:26:53 GMT

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.

Re: ArcGIS Pro and local SQL Express DB

AZendel — Wed, 11 May 2022 18:41:30 GMT

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.

Re: ArcGIS Pro and local SQL Express DB

BrunoBlanco — Tue, 17 Jan 2023 19:10:42 GMT

100% agree. In our case, we have several rural users who still use SQL Server Express databases extensively for their daily mission-critical work (9-1-1 addressing). They have not fully transitioned to Pro yet and will not transition until it's absolutely necessary. Honestly? Things like this are a huge part of the reason why.

Re: ArcGIS Pro and local SQL Express DB

JackYoder — Thu, 27 Mar 2025 21:08:02 GMT

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.

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Fri, 28 Mar 2025 10:19:15 GMT

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_A188C2B1C8C54C7DA3233892FC742552

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!

Re: ArcGIS Pro and local SQL Express DB

JackYoder — Fri, 28 Mar 2025 14:09:00 GMT

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.

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Fri, 28 Mar 2025 20:32:36 GMT

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:

Re: ArcGIS Pro and local SQL Express DB

JackYoder — Fri, 28 Mar 2025 21:27:59 GMT

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?

Re: ArcGIS Pro and local SQL Express DB

JackYoder — Mon, 31 Mar 2025 01:26:10 GMT

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.

Re: ArcGIS Pro and local SQL Express DB

George_Thompson — Mon, 31 Mar 2025 10:32:13 GMT

Here is the documentation on creating a workgroup geodatabase: https://enterprise.arcgis.com/en/documentation/install/database-server/10.8/create-geodatabase-database-server.htm

Here is the documentation on installing SQL Express (for reference): https://enterprise.arcgis.com/en/documentation/install/database-server/10.8/setup-database-server.htm

Here is how you connect to the Workgroup GDB in Pro: https://support.esri.com/en-us/knowledge-base/faq-is-it-possible-to-create-a-workgroup-geodatabase-in-000018326

This is all for future reference.