topic Re: SQL Connection & Query for versioned data? in Geodatabase Questions

SQL Connection & Query for versioned data?

ZacharyHart — Fri, 01 Jul 2022 13:49:26 GMT

When making a direct connection to eGDB tables via (for example) SSMS (Management Studio) for a GDB that has (traditional) versioning enabled, I'm querying an old version of the data. The data is post & reconciled to the default version daily.

If I look at the list of (default/Esri created) views and query against the same table via that view, I see the default version.

I am aware of the Esri created stored procedure for switching versions, but what is the version which querying the table, not via a view? I cannot see any way to determine which version state this table is other than that (based on editing dates) it is very old.

Re: SQL Connection & Query for versioned data?

George_Thompson — Fri, 01 Jul 2022 14:28:37 GMT

I am not sure you can determine that information based on querying just the base table. There may be edits that have not be compressed and pushed to the base table.

I would not query the base table (directly) to determine anything in terms of "current state" when it is versioned (traditionally).

I hope I am providing a response to your question. If not, let us know.

Re: SQL Connection & Query for versioned data?

ZacharyHart — Fri, 01 Jul 2022 14:40:31 GMT

so does 'base table' represent the default version of the GDB?

The versions are post & reconciled to the default version daily and and compression is run weekly.

If I query say the top 10 records sorted by 'last_edited_date' descending, i'm seeing stuff from 2019 which would be the inception of this particular GDB.

Re: SQL Connection & Query for versioned data?

George_Thompson — Fri, 01 Jul 2022 14:53:54 GMT

"Base table" does not equal or represent DEFAULT, unless it is not versioned or all the delta tables are empty.

Based on that example above, it could mean that those records have not ever been edited and / or there are still edits that have not compressed.

That would be the best reason to use the versioned view.

Re: SQL Connection & Query for versioned data?

ZacharyHart — Fri, 01 Jul 2022 15:33:04 GMT

I should perhaps clarify: according to my query, the most recent edits in the base table show as circa 2019. Which would then mean none of the edits have been compressed?

Re: SQL Connection & Query for versioned data?

George_Thompson — Fri, 01 Jul 2022 16:52:58 GMT

Potentially or those specific records have not be updated since the beginning.

Do you have replicas in the versioning mix or offline copies of the data?

Have you tried the reconcile process with the GP tool (https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/reconcile-versions.htm) and this setting: "Reconcile blocking versions only—Versions that are blocking the target version from compressing will be reconciled. This option uses the recommended reconcile order."?

Re: SQL Connection & Query for versioned data?

ZacharyHart — Fri, 01 Jul 2022 17:21:36 GMT

To the first point, what I'm saying is the most recent record in the base table (according to editor tracking) is from 2019-09-18 15:23:13.0 and therefore every other record in that table is older than that. (And would therefore indicate that the base table has never been updated!?)

An astute guess! We do indeed have offline replicas in the mix here. Everything is configured according to this Esri doc here. The FS is configured to create a version per user. We have a version of the data to control all the field user/offline versions as described in the article and we would most closely resemble 'Workflow 3'

The script responsible for dealing with the versions (using code similar to what is cited in the doc) utilizes the same GP tool you reference.

The code builds a list of versions who's parent version is the DB version I mention above and then reconciles them, and then reconciles the controlling version to the default version. Partial code here:

verReconcileList = [] #Create a list of children of the Data Inspector version: for version in versions: if version.parentVersionName == inspectionVersion: verReconcileList.append(version.name) versionName = (version.name.split(".")[1]).split("/")[0] lastModified = version.lastModified print("{0} Last modified: {1}".format(versionName,lastModified)) # Perform maintenance if versions are found, otherwise there is no maintenance to perform. if len(verReconcileList)>0: # Get a list of versions from the database versions = arcpy.da.ListVersions(workspace) # Reconcile user versions to CollectedFieldData version arcpy.ReconcileVersions_management(workspace, "ALL_VERSIONS", inspectionVersion, verReconcileList, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", "c:/temp/reconcilelogCollected.txt") # Reconcile CollectedFieldData version to Default version arcpy.ReconcileVersions_management(workspace, "ALL_VERSIONS", "sde.DEFAULT", inspectionVersion, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", "c:/temp/reconcilelogDefault.txt")

In order to control lag between user syncs and making updates visible to web services and GIS staff, this script is run twice a day.

We know it's working because we see the edits in both the 'Inspector' version and the default version.

Everything looks fine in the Esri view of the data table in SSMS as well...just not the base table. To reiterate: looking at the base table, it appears that no records have ever been updated here.

The best compress end state count we can achieve is 61 which isn't too surprising given that Esri's offline data mechanism is apparently prone to creating orphan versions/replicas (according to the Geodata analyst i've worked with in the past).

I just cannot understand how this base table reflects no changes/edits...looks like a snapshot of the data from ~3 years ago....

Re: SQL Connection & Query for versioned data?

George_Thompson — Fri, 01 Jul 2022 20:18:17 GMT

I bet the replicas are pinning a state and not allowing the edits to be pushed to the base table. Here is a great blog that talks about how to get a better compress with replicas: https://community.esri.com/t5/esri-technical-support-blog/tips-for-compressing-with-existing-geodatabase/ba-p/898380

I have attached a SQL script (sql_versioning_stats.txt) that will report much of the versioning information. I recommend that you run this on a TEST / DEV database first. Is written for SDE schema enterprise geodatabase.

There is also another one (version_inventory.txt) that returns the versions and when they were last touched (edited). It helps in determining if a version is still in use or stale.

I have not run these in a long time, so no promises.