2

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.

7
  • 2
    If you have a versioned table in an enterprise geodatabase, you can't just add columns using SQL -- doing so would corrupt the Adds table.
    – Vince
    Commented Nov 1, 2022 at 18:26
  • I second @Vince, there's more than just the one table in an enterprise database, there is a register of fields, their types and also their indices; subtypes and domains also are rows in a table where the fields must be the same. If you want to add fields use ArcGIS AddField to update the required rows. Geodatabases have been like this for a long time, breaking a personal geodatabase by editing with Access or a DBF by editing with Excel is a concept that predates GIS.SE, by now most users know better. Not to say you can't edit the backend, just be very careful if you do (and keep a backup) Commented Nov 2, 2022 at 6:22
  • Even with exclusively Query Layers, the Pro app stores the SQL and type definitions. Much like a stored procedure or view wouldn't like you changing the table definition, each time you modify the table, you really need to drop and recreate the layer.
    – Vince
    Commented Nov 2, 2022 at 11:34
  • Thanks, @Vince. Isn't enterprise geodatabases different from (enterprise) database connections? All of us use basic licenses so we can't even create enterprise geodatabases in our SQL databases. We work in a small org and don't work with enterprise geodatabases and versioning in ArcGIS.
    – aakims
    Commented Nov 2, 2022 at 13:24
  • 1
    If you don't have a geodatabase, you can't have schema locks. You should certainly Refresh the connection after database changes.
    – Vince
    Commented Nov 2, 2022 at 13:27

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.