nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

JackYoder

I think I found a workaround for me, but it is quite AWKWARD!

First, I tried using ArcGIS Pro to create an SQLite geodatabase. Not sure if I did something wrong, but I ended up with a folder with a .gdb extension full of files. There was no single file database that I could make an ODBC connection with.

My next attempt was to create an SQLite database directly in SQLite and gave it the .SQLite extension. ArcGIS pro recognized it. I was able to connect to it with Catalog as well as to my SQL Express database. I did a cut and paste of feature set table to put a copy in the SQLite database. Unlike my SQL Express database where I could not edit the layer, I was able to add the same layer from the SQLite database and edit it just fine.

My next step, like you was to download a free SQLite ODBC driver. I was able to create a DSN just fine, but when I tried in MS Access to link the table, I got an error that started out "_SQLSpecial....". I tried with several tables including those without geometry and got the same error. The ODBC driver I downloaded was from GM Software. I looked at the one from the .de site, but because it was http, not https, I was a bit spooked and looked for malware reports. I'm not sure if they are legit, but I found some that indicated SilverFox, so I did not download that one. I considered trying the Devart one, but decided not to because I did not want to pay that much for it.

My workaround is this: In the old days, things were straight forward. I used an MS Access personal gdb with ArcMap. My forms, queries, and reports were in Access and I used ArcMap for the geo tasks and maps.

I had previously converted my MS Access database to SQL Express. I had to deal with some differences in time and date storage, but it as pretty straight forward. MS Access became the front end with the queries, forms, and reports to linked tables that were stored in SQL Express. My plan was to use ArcGIS Pro for the geo tasks, but found I could not edit using it.

(To be fair, perhaps there is a way to create the SQL Express database in a way it can be edited and I did something wrong creating it, but I can not find a clear way to do that with only a personal use license (I have one for both ArcMap an ArcGIS Pro).

My workaround is to keep two databases. My working database will continue to be the one in SQL Express. In addition to the MS Access front end for it, it also supports an web site where non-geo data is published from it. My second database will be in the single file SQLite database or perhaps the .gdb folder database. Either way, I can connect to all of them in ArcGIS Pro and edit either of these. When map edits are needed I will do it with ArcGIS Pro in one of these two SQLite databases. I will then use Catalog to copy the updated table to the SQL Express database. I will need to do this each time I make mapping edits. I will also need to uses the apply symbology tool in the Geotools to get my symbology back from the original. I can then delete it.

As I said, this is an awkward workaround but it is the best I have unless and until I can find a way to edit an SQL Express database in ArcGIS Pro with only a personal use license.

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.

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.

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?

db3.jpg

DB1.jpg

db2.jpg

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

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

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!

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.

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.

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

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

ZacharyHart

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.

George_Thompson

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.

ZacharyHart

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??

George_Thompson

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)

ZacharyHart

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

George_Thompson

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.