topic Re: (again): A tool to transfer the Add\Delete tables to the business table, in Data Management Questions

A tool to transfer the Add\Delete tables to the business table,

JamalNUMAN — Tue, 13 Sep 2022 05:46:38 GMT

(again): A tool to transfer the Add\Delete tables to the business table,

I???m wondering if there is simple tool that can transfer the Add\Delete tables to the business table. I got the attached model tool but it appears not to do the job

[ATTACH=CONFIG]31580[/ATTACH], [ATTACH=CONFIG]31581[/ATTACH]

What other tools might transfer the Add\Delete tables to the business table

Thank you

Best

Jamal

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Wed, 19 Feb 2014 15:49:14 GMT

Original User: mboeringa2010

Jamal,

What do you mean with "attached model"? I only see a table... you probably mean you have made some edits in a replica or version, and now wish to transfer these to your main geodatabase and add them to DEFAULT and possibly compress to state 0?

Other than using the ESRI supplied tools (synchronize replicas, reconciling & posting version changes, and compress your geodatabase), it is unwise to start messing with the tables in SQL Server Management Studio. You may wreck your geodatabase.

If you need to see the state of DEFAULT, including all the Adds and Deletes in an application other than ArcGIS / non-ESRI software (e.g. in SQL Server Managament Studio), you can connect to the Versioned View instead of the base table. It should show you the latest state of DEFAULT including all changes in the Adds and Deletes tables (at least as far as reconciled and posted to DEFAULT, if you don't reconcile and post to DEFAULT, the changes of course won't be visible in DEFAULT).

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Wed, 19 Feb 2014 17:24:05 GMT

Original User: Jamal432@gmail.com

Jamal,

What do you mean with "attached model"? I only see a table... you probably mean you have made some edits in a replica or version, and now wish to transfer these to your main geodatabase and add them to DEFAULT and possibly compress to state 0?

Other than using the ESRI supplied tools (synchronize replicas, reconciling & posting version changes, and compress your geodatabase), it is unwise to start messing with the tables in SQL Server Management Studio. You may wreck your geodatabase.

If you need to see the state of DEFAULT, including all the Adds and Deletes in an application other than ArcGIS / non-ESRI software (e.g. in SQL Server Managament Studio), you can connect to the Versioned View instead of the base table. It should show you the latest state of DEFAULT including all changes in the Adds and Deletes tables (at least as far as reconciled and posted to DEFAULT, if you don't reconcile and post to DEFAULT, the changes of course won't be visible in DEFAULT).

Sorry Marco for the confusion.

1. The model I used is now attached

2. My enterprise geodatabase has no versions. It is edited on the default. Then there is no need to reconcile and post or synchronize

[ATTACH=CONFIG]31583[/ATTACH]

3. I have just visited the enterprise geodatabase via SQL to have a look but not to play with it. I found out that the Add\Delete tables are not transferred to the business tables even after applying the �??compress database�?� tool.

[ATTACH=CONFIG]31584[/ATTACH]

What might be the issue? Which tool is supposed to be used to get the data updated in the business table?

Re: (again): A tool to transfer the Add\Delete tables to the business table,

WilliamCraft — Wed, 19 Feb 2014 19:03:03 GMT

The "Is Replica" flag is set to True in your most recent set of screenshots. Is the dbo.DEFAULT version of the Communities_Points database set up for two-way replication or one-way replication where it is the child?

What is listed when you run the following query against your databases?

select * from Communities_Points.DBO.versions

I'm particularly looking to know if there are any SYNC_SEND_XXX versions which have not been synchronized. You may want to query the GDB_REPLICALOG table as well to see if there are any errors reported in synchronization.

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Wed, 19 Feb 2014 19:10:30 GMT

Original User: mboeringa2010

What might be the issue? Which tool is supposed to be used to get the data updated in the business table?

Jamal,

Just a quick observation, but based on your first screenshot in the last post, it shows the dataset being a "replica" ("Is Replica" is TRUE).

Are you actually looking in the right database, and compressing the right one? Shouldn't you be synchronizing with the parent and compress there?

Other thing: did you actually select "Reconcile and Post with parent version" in the Synchronize Changes wizard? If not, the data will not have been automatically reconciled and posted to your DEFAULT, and you will have to manually reconcile and post the replica version against DEFAULT and subsequently compress.

Please also note that even if you don't create additional versions, replication still requires the parent database to be versioned, and a Replica Version will be created upon creating the replica. This is why you need to reconcile and post this replica in the parent database against DEFAULT, and compress, before the edits will show up in the base table.

Re: (again): A tool to transfer the Add\Delete tables to the business table,

JamalNUMAN — Sat, 08 Mar 2014 17:35:51 GMT

Jamal,

Just a quick observation, but based on your first screenshot in the last post, it shows the dataset being a "replica" ("Is Replica" is TRUE).

Are you actually looking in the right database, and compressing the right one? Shouldn't you be synchronizing with the parent and compress there?

Other thing: did you actually select "Reconcile and Post with parent version" in the Synchronize Changes wizard? If not, the data will not have been automatically reconciled and posted to your DEFAULT, and you will have to manually reconcile and post the replica version against DEFAULT and subsequently compress.

Please also note that even if you don't create additional versions, replication still requires the parent database to be versioned, and a Replica Version will be created upon creating the replica. This is why you need to reconcile and post this replica in the parent database against DEFAULT, and compress, before the edits will show up in the base table.

Thank you very much William and Marco for the help,

All what I wanted is to have tools that can transfer the updates stored on the Add\Delete tables to the physical (business).

[ATTACH=CONFIG]32046[/ATTACH]

Do the tools depicted in the screenshot below can transfer the values stored in the Add\Delete tables to the business table?
[ATTACH=CONFIG]32048[/ATTACH]

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Sat, 08 Mar 2014 18:38:17 GMT

Original User: crafty762

Do the tools depicted in the screenshot below can transfer the values stored in the Add\Delete tables to the business table?

The Compress GP tool you show in your model is responsible for moving rows from the A and D tables into the base table, assuming that no child versions reference the state IDs of those particular rows. Reconciling and posting beforehand (which you've correctly delineated in your model) is what moves your state ID pointers in the geodatabase. That is why reconciling, posting, and compressing are such critical parts of the geodatabase maintenance process.

Re: (again): A tool to transfer the Add\Delete tables to the business table,

JamalNUMAN — Sat, 08 Mar 2014 19:05:13 GMT

The Compress GP tool you show in your model is responsible for moving rows from the A and D tables into the base table, assuming that no child versions reference the state IDs of those particular rows. Reconciling and posting beforehand (which you've correctly delineated in your model) is what moves your state ID pointers in the geodatabase. That is why reconciling, posting, and compressing are such critical parts of the geodatabase maintenance process.

Thanks William.

Sure, the �??reconcile versions�?� does transfer the updates from the parent to child and from child to parent (if the �??post versions after reconcile�?� is checked).

[ATTACH=CONFIG]32052[/ATTACH]

Now, being the updates are reflected in the sde.default and its versions by the �??reconcile version�?� tool, the �??communities�?� in the SQL server doesn�??t match the one on the ArcGIS

Number of records of the �??communities�?� in the SQL is 781
Number of records of the �??communities�?� in the ArcGIS is 788

[ATTACH=CONFIG]32051[/ATTACH]

Which tools can be used to have Number of records of the �??communities�?� in the SQL and in ArcGIS equal?

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Sat, 08 Mar 2014 19:26:51 GMT

Original User: crafty762

Please post the results from the following query against your geodatabase:

select * from Communities_Points.DBO.versions

If everything has been reconciled and posted, then compressed, I agree that the base table should match what's in SDE.DEFAULT (or the versioned view of the SDE.DEFAULT as Marco mentions above) in terms of record count. It feels like something else is locking a particular state and preventing its edits from being moved from the delta tables to base.

Re: (again): A tool to transfer the Add\Delete tables to the business table,

MarcoBoeringa — Sat, 08 Mar 2014 19:35:23 GMT

Now, being the updates are reflected in the sde.default and its versions by the �??reconcile version�?� tool, the �??communities�?� in the SQL server doesn�??t match the one on the ArcGIS

Number of records of the �??communities�?� in the SQL is 781
Number of records of the �??communities�?� in the ArcGIS is 788

...

Which tools can be used to have Number of records of the �??communities�?� in the SQL and in ArcGIS equal?

That process is carried out by the Compress operation, step 2 in your ModelBuilder model. If the Compress failed to do that, than there are still versions or replicas (which as William correctly stated are also technically versions) that haven't been reconciled & posted properly.

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Fri, 04 Apr 2014 17:48:29 GMT

Original User: Jamal432@gmail.com

That process is carried out by the Compress operation, step 2 in your ModelBuilder model. If the Compress failed to do that, than there are still versions or replicas (which as William correctly stated are also technically versions) that haven't been reconciled & posted properly.

Thank you guys for the massive help and support.

To avoid the issue of reconcile\post, I have created *.mdf geodatabase (M.mdf) that contains one layer (A). Before editing the layer A, there was no Add\Delete tables for the A layer in the database.

[ATTACH=CONFIG]32815[/ATTACH]

Now, the A layer is edited. Two features are deleted and three are added. These two transactions are recorded in the add\delete tables.

[ATTACH=CONFIG]32816[/ATTACH], [ATTACH=CONFIG]32817[/ATTACH]

Next, I applied the model that contains compress, rebuild indexes and analyze datasets but the add\delete tables still keep the added and deleted values and this should affect the performance.

[ATTACH=CONFIG]32818[/ATTACH], [ATTACH=CONFIG]32819[/ATTACH]

Then with which tools the values kept in the add\delete tables are transferred to the business table so that the add\deletes tables get empty?

Re: (again): A tool to transfer the Add\Delete tables to the business table,

WilliamCraft — Fri, 04 Apr 2014 18:00:43 GMT

Are there other connections to the geodatabase besides the one from which you're attempting compress? Client connections can create state locks and therefore prevent records from A & D tables getting written to base. When you run compress, please ensure there are no other connections.

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Fri, 04 Apr 2014 18:43:05 GMT

Original User: Jamal432@gmail.com

Are there other connections to the geodatabase besides the one from which you're attempting compress? Client connections can create state locks and therefore prevent records from A & D tables getting written to base. When you run compress, please ensure there are no other connections.

Thanks William.

The screenshots show what I have as versions, connections and locks

[ATTACH=CONFIG]32821[/ATTACH], [ATTACH=CONFIG]32822[/ATTACH], [ATTACH=CONFIG]32823[/ATTACH]

What other issues should I check?

Re: (again): A tool to transfer the Add\Delete tables to the business table,

WilliamCraft — Fri, 04 Apr 2014 18:52:09 GMT

Let's try this...

Please post the results of the following query:

SELECT * FROM DBO.VERSIONS;

I am curious to know the state ID of your DEFAULT version.

Also, please post the results of the DIAGNOSE_METADATA and DIAGNOSE_TABLES commands. Run the DIAGNOSE_TABLES command on the entire geodatabase by setting the -V parameter to ALL and leaving out the -r parameter. Below is the syntax for both commands:

sdegdbrepair -o diagnose_metadata -d <{ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}>
[-H <home_directory>] [-u <ArcSDE_admin_user>] [-p <ArcSDE_admin_password>]
[-D <database_name>] [-s <server_name>] [-i <master_geodatabase_service>:<user_schema>] [-q]

sdegdbrepair -o diagnose_tables -d <{ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}>
[-r versioned table name | file=<table_list>] [-V {ALL | NONE | <version_name>} | STATE:<state_id>]
[-H <home_directory>] [-u <ArcSDE_admin_user>] [-p <ArcSDE_admin_password>]
[-D <database_name>] [-s <server_name>] [-i <master_geodatabase_service>:<user_schema>] [-q]

If no issues come back from these commands, then it may be time to open an Esri ticket to understand why your records won't move to the business table. Let me know the results, thanks.

Re: (again): A tool to transfer the Add\Delete tables to the business table,

Anonymous User — Fri, 04 Apr 2014 22:00:12 GMT

Original User: asrujit

From the screen shots I can see that you are running the Model from the ArcMap interface.

1) Did you save the edits and stop editing?
2) Try to close the ArcMap after editing and then run the Model from ArcCatalog. Does this help?

You must understand that just running the compress will not push all records from the delta tables to the Base table. There should be no one editing the data, when you are attempting the compress if you want a "full compress".

Re: (again): A tool to transfer the Add\Delete tables to the business table,

JamalNUMAN — Sat, 05 Apr 2014 16:40:05 GMT

From the screen shots I can see that you are running the Model from the ArcMap interface.

1) Did you save the edits and stop editing?
2) Try to close the ArcMap after editing and then run the Model from ArcCatalog. Does this help?

You must understand that just running the compress will not push all records from the delta tables to the Base table. There should be no one editing the data, when you are attempting the compress if you want a "full compress".

Many thanks William and Asrujit,

Perfect Asrujit�?�

It appears that I need to save the edit, to close the ArcMap and next to apply the �??compress�?� tool.

The add\delete tables get empty and the business table is updated.

I�??ll be reporting if any failure happens in the future with this approach.

Best

Jamal

Re: (again): A tool to transfer the Add\Delete tables to the business table,

TimothyHales — Fri, 28 Aug 2015 19:17:11 GMT

Re: (again): A tool to transfer the Add\Delete tables to the business table,

TimothyHales — Fri, 28 Aug 2015 19:18:38 GMT

Re: (again): A tool to transfer the Add\Delete tables to the business table,

TimothyHales — Fri, 28 Aug 2015 19:19:54 GMT

Re: (again): A tool to transfer the Add\Delete tables to the business table,

TimothyHales — Fri, 28 Aug 2015 19:21:54 GMT

Images from the original question