ArcGIS Pro unable to add SQL database object when new fields had been added

Question

About 90% of the layers on my maps are query layers from SQL Server database connection. My typical workflow is to drag and drop the spatial objects from the Catalog pane into the map frame and then work on symbology. Sometimes the work can get iterative, where I would keep making edits in the source object from the SQL side.

As long as the schema of the source object stays the same when I make edits, the layers have no problem showing the updated data. But it's been frustrating trying to bring the object that's been recreated with additional fields but with the same name.

After I make the new table in SQL, I refresh the database connection in Catalog, then drag and drop the updated table into the map. It tells me that it's adding data, but it just hangs there forever. I'm not talking about tables with millions of records. I just waited for ~30 minutes for a table with 70 rows and maybe 150 columns, but my coworkers and I experienced this with tables of various sizes. We always have to kill the application, restarting, and try something else. We have tried:

• Creating a new database connection with a different connection name.
• Updating source query of the existing layer that was created before the table was updated by going into layer properties > set data source OR edit query (the pencil icon).
• Trying to clear all local feature caches (not even sure if this is relevant).
• Trying to add the table by Map > Add Data > Query Layer.
• Trying to update query using ArcPy and accessing layer's CIM.

Nothing worked. Was using 2.7 and just upgraded to 3.0 for this reason, and it's doing the same thing. We think aprx stores the metadata from the initial version of the table, and when we try to bring in the object with the same name but different metadata, it can't merge two versions. Our suspicion is based on the fact that 1) the SQL object can be brought into a different aprx with no problem (but not in new frames in the same aprx) and 2) if we create another SQL table with exactly the same data but a different name, it works.

We've had to rebuild project files with dozens of layers (ready for export) from scratch because we are unable to add new data fields. Obviously, not ideal. I've searched online for hours for this, but surprisingly didn't see anyone experiencing this issue.

Answer 1

In most GIS environments, static or slowly changing data structures are the rule. Most GIS tools are organized to take advantage of the work of initial data discovery, caching the names and types of columns as they are first discovered. If you remove or significantly alter fields, you corrupt the project files of any document that references the previously existing table. The only way to prevent this corruption is to change practice.

I had a project during the start of the pandemic where epidemiologists and other modelers were making large schema changes frequently, and wanted the new data in front of decision-makers for early-morning briefings as soon as possible.

The model I settled on, since I was supporting five modeling teams concurrently, and the old models were seeing daily content updates in addition to biweekly schema updates, was to utilize a naming protocol to enforce a reusable data structure.

When a new team was contracted, I'd populate their data with a _t10 suffix and create corresponding views with a _v10 suffix. Map services were then constructed using the views, with _100, _101, etc suffixes, allowing the UI team flexibility. Dashboards were created from the services named in parallel to the service.

Then if a new column was added to the table, I could create a new _v11 view that wouldn't break any existing service, and generate a new _110 service based on the last _10x service.

If a column changed type, I'd create a new table, named _t11 (or _t12, whatever was higher than the highest existing view) or _v20, depending on structural significance of the change(s). Corresponding views and services would be created as well.

The benefit here is that the loaders were structured to handle content changes, so the same daily updates could update all active services while QA was performed on the new dashboards.

As new services were adopted, the older services were stopped, then eventually deleted (though the project files to publish them were retained for records management reasons).

So, if you have a highly dynamic data situation, you need to enforce enough consistency within the database so that tools which use the content definitions as a snapshot in time can continue to function. This requires rigorous application of a methodology to preserve reverse compatibility.