topic Re: SQL view to display the last reconcile/post dates in SDE tables in Geodatabase Questions

SQL view to display the last reconcile/post dates in SDE tables

JanTeisinger — Wed, 20 Jun 2018 19:32:36 GMT

Hi, I would like to create a view that would show the dates for the last reconcile and post operation for each version in GDB. I understand each state has a closing date and each version can have last touched data. However I am having a difficult time figuring out at which point either the states or lineages would be considered "reconciled/posted". I was trying to use a combination of SDE_states, SDE_versions, SDE_lineages_modified and SDE_state_lineages.

Would anyone have SQL code or ideas how to accomplish this?

The idea here is to create a report that would list which versions need to be reconciled and posted within a certain time period, to hold our users accountable.

Thank you!

Jan

Re: SQL view to display the last reconcile/post dates in SDE tables

chrisSchmuck — Mon, 16 Jul 2018 13:38:32 GMT

I would also appreciate an example of how to construct a sql query returning a list of versions with last modified dates.

Re: SQL view to display the last reconcile/post dates in SDE tables

chrisSchmuck — Mon, 16 Jul 2018 15:01:44 GMT

Jan-

After I viewed the relational diagram, you could use the following query to obtain the last time a versions was modified, which includes edit and rec/post operations.

SELECT versions.name, states.closing_time
from versions
inner join states on versions.state_id = states.state_id;

http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-sql-server/pdf/sdesystables_diagram.pdf

Re: SQL view to display the last reconcile/post dates in SDE tables

JanTeisinger — Tue, 31 Jul 2018 15:11:46 GMT

Hi, thanks for providing me with the query. This query is helpful to find out when the version was last touched, however this would still not provide the date when the last reconcile/post operation was run. Is there some special flag indicating which state/lineage had reconcile/post run on it specifically? Thanks for your help!

Re: SQL view to display the last reconcile/post dates in SDE tables

ashizzle — Fri, 21 Mar 2025 04:18:56 GMT

SELECT edit_moment FROM sde.sde_branch_tables_modified where branch_id = 0 order by edit_moment desc limit 1

0 = default. see the rest of branches in sde_branches.