•Confirm the Rollback was Successful
After loading data into a database, issues may arise that necessitate removing the data and the data package from the database. Removing a complete data package is called a Rollback. Each data import session is automatically assigned an EBATCH number. The Rollback feature in EDP allows the removal of data by determining the EBATCH number associated with the bad data, and then removing each record from all of the tables related to that EBATCH.
Notes: •The Rollback will not be successful if the referential integrity of the data is broken as a result of the rollback. For example, if a reference value was added using EDP with other data — and it is determined that this other data is faulty — it is only possible to remove that data if no other data in the database uses the reference values associated with the reference values in the same EBATCH. •If any non-EQuIS tables exist in the database, their names must not contain special characters. If special characters do exist in the table names, the Rollback may fail with the error:
|
1.Open EDP by clicking the icon 
located in the Import group of the Home tab.
2.Click Rollback (located in the Package group on the Professional tab). The EQuIS Import Rollback window contains the same information as the ST_EDD_BATCH table. Identify the exact EBATCH to be removed. The EBATCH can be found following these steps:
a.In EQuIS Professional click Data Tables 
located in the Open group on the Home tab.
b.Select the data table with data to roll back (e.g, select DT_SAMPLE).
c.Within this data table, scroll to the right until the EBATCH field is the right-most column.
d.Click the EBATCH column header twice to sort the column in descending order (so that the small arrow in the header points down). Make a note of the EBATCH value to roll back.
Note: Commit types included in the ST_EDD_BATCH table correlate to the commit types displayed in the EDP Commit button drop-down menu as follows:
|
3.Highlight the EBATCH record to roll back.
4.Click Next >.
5.To confirm, click the record to roll back and then click Rollback >.
a.Click the Plus + button to the left of the EDD row to list the tables in the database that contain records for this EBATCH:
6.Click Finish.
Once the process is completed, confirm the Rollback process was successful and that the data was completely removed from the database.
Confirm the Rollback was Successful
1.Close EDP.
2.In EQuIS Professional, click Data Tables .
3.Open the data table on which the Rollback was performed (e.g., DT_SAMPLE) and double-click the EBATCH column. The EBATCH selected to rollback should no longer be listed in the corresponding data table. Note that the rollback information is stored in the ST_EDD_BATCH table.
The error type shown below may be encountered upon Rollback and is designed to keep referential integrity between tables.
System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK__[table]". The conflict occurred in database "[database]", table "dbo.[table]". The statement has been terminated. at EarthSoft.EDP.Forms.EddRollback.ProcessRollback() in C:\Hg\EarthSoft.hotfix\EDP\Forms\EddRollback.vb:line [line number] |
This error indicates that EDP was unable to complete the rollback process, because the completed process would result in incomplete referential integrity between tables.
For example, using an "Insert and Update" commit type would update existing reference values in the database with the new EBATCH value for one or more records that may also be used in other records. Attempting to roll back these data would roll back the reference values and would result in the 'other' data records not having the parent reference value.
Inspecting the second sentence of the error indicates which table contains the ‘other’ data records which would cause the referential integrity issues. Find the parent tables for the table referenced in the error by changing this EBATCH to its original value (recommended) or removing the value altogether, which should allow a successful rollback. Prior to modifying the EBATCH, the user should view all records related to the EBATCH numbers in question to ensure that only the intended data is rolled back. Care should be taken when considering modifying any EBATCH numbers, as doing so would result in values that are no longer associated in the database with their original submission. If the desire is to remove all the data associated with these records, all the associated EBATCH numbers can be rolled back. The rollback order of these EBATCH numbers needs to be in order of child record EBATCH first, then parent record EBATCH. See the Commit Type example to see when existing EBATCH numbers are replaced based on commit type.
If you are unsure what EBATCHs are associated, or if you wish to perform a rollback of EBATCHs in the order they were loaded, follow these steps:
1.In step 5a of the rollback steps up above, take note of what tables have data associated with the EBATCH.
2.Open the table listed in the error message and find the parent table(s). Right click a row > Related Tables > Parent Tables. See Add Related Tables (Parent or Child) for more information.
3.Compare the list in steps 1 and 2 to find what parent tables have data associated with the EBATCH. It is possible that the issue could be caused by a grandparent record as well.
4.Open those parent table(s).
5.Filter the table(s) for the EBATCH to be rolled back.
6.Select all the records.
7.Right click a row > Related Tables > Child Tables > Select the table listed in the error message.
8.Review EBATCH for each of these child records for value(s) that does not match the EBATCH to be rolled back.
9.After reviewing the parent records and child records, the decision must be made on whether to rollback both parent-child records, and the order of the rollback EBATCHs or to modify EBATCH values.
Warnings: •Before modifying EBATCH numbers, create a backup of the database. Rollbacks cannot be undone. |