Rollback

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:

System.Data.SyntaxErrorException: Syntax error in the expression.

Perform an EDD Rollback

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.

EDP_Rollback

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:

ST_EDD_BATCH Table	EDP Commit Menu
Insert	Insert only
Merge	Insert and Merge
MergeOnly	Merge Only
Replace	Insert and Replace
ReplaceOnly	Replace Only
Update	Insert and Update
UpdateOnly	Update Only

3.Highlight the EBATCH record to roll back.

4.Click Next >.

5.To confirm, click the record to roll back and then click Rollback >.

6.Click Finish.

Once the process is completed, it is possible to 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 the ST_EDD_BATCH table.

Rollback Errors

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 updated reference values with the new ebatch value for one or more records, which may be used in other records also. Attempting to roll back this data would roll back the reference values, resulting in the 'other' data records not having the parent reference value.

Inspecting the second sentence of the error indicates which table contains the referential integrity issues. Changing this ebatch to its original value or a different value than the one being rolled back, or removing the value altogether, 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 to modify any ebatch numbers, as doing so results in values that are no longer associated in the database with their original submission.

Warnings:

•Before modifying ebatch numbers, create a backup of the database. Rollbacks cannot be undone.

•A bug causing the row count on the EDP EDD Rollback Form to be doubled when using any commit type other than Insert Only, has been fixed.