How to remove MSSQL records associated with a failed dataset migration or dataset copy - Generate Delete Data from All Tables by TenantID

If a situation occurs where a dataset is partially migrated to MSSQL, but fails; or a MSSQL dataset fails part way through a Copy or Dataset Import, then the following process can be used to remove the orphaned entries so that the database is as lean and efficient as possible.

Process:

1. Execute the following query to generate a list of delete statements that can then be executed to remove rows for a specific TenantID:

use AGRIS;

select 'delete ' + s.name + '.' + t.name + ' where TenantID = @TenantID'
from sys.tables t
join sys.schemas s
on t.schema_id = s.schema_id
where exists (select 1
from AGRIS.sys.columns c
where t.object_id = c.object_id
and c.name = 'TenantID')

2. Create a new query and add the following statement as the first line:

declare @TenantID as varchar(36) = 'tenant id goes here';

NOTE: Substitute in the actual TenantID in question (i.e. 9B6343B8-15B7-4DB9-B119-A83801194888). To identify the TenantID, refer to the [Accelerator].[dbo].[Tenant] table (i.e. SELECT * FROM [Accelerator].[dbo].[Tenant]) and identify the TenantID that is associated with the partially migrated/corrupted dataset in question. A001 is dataset 001, A002, is dataset 002, etc.

3. Copy\paste the resulting delete statements from step 1 into the new query started in step 2.

NOTE: The first lines of the new query should look similar to this assuming we continue the example from step 2:

declare @TenantID as varchar(36) = '9B6343B8-15B7-4DB9-B119-A83801194888';

delete dbo.I30_CARD_DETAILS where TenantID = @TenantID
delete dbo.W24_RMT_PR_DEF_DTL where TenantID = @TenantID
delete dbo.A132_EQ_YARGUS where TenantID = @TenantID

4. Execute the script assembled from steps 2 and 3.

5. Manually remove the invalid dataset reference from the U05_DTA_SETS table in the AGRIS database. (The TenantIDs found in U05 all point back to A000 dataset, which refers to tenant neutral data. As such, the specific dataset reference in question does not get removed automatically from U05 when items 1-4 above are completed.)

As an example, if we were completing the removal of dataset 008 from the database name AGRIS, then we would use this command:

use AGRIS;
DELETE FROM U05_DTA_SETS WHERE U05_DSET_NUM_S=008

NOTE: It is possible that a U05_DSET_NUM_S reference for this dataset may not exist depending on what did or did not complete during the dataset import/copy operation.

6. Manually remove the invalid dataset reference from the Tenant table in the ACCELERATOR database.

As an example, if we were completing the removal of the TenantID used throughout this document, 9B6343B8-15B7-4DB9-B119-A83801194888, then we would use this command:

use ACCELERATOR;
DELETE FROM Tenant WHERE TenantID='9B6343B8-15B7-4DB9-B119-A83801194888'

NOTE: It is possible that a TenantID reference for this dataset may not exist in the [Accelerator].[dbo].[Tenant] table depending on what did or did not complete during the dataset import/copy operation.

AGRIS Customer Documentation

How to remove MSSQL records associated with a failed dataset migration or dataset copy - Generate Delete Data from All Tables by TenantID

Related articles