Troubleshooting WebTMA7 On-Premise Upgrade Utility Errors

Occasionally data errors may be encountered when running the WebTMA 7 Upgrade Utility.

Typically, two types of errors occur:

• Duplicate data errors
• Orphaned data record errors

Error information is available in the ConversionLog.txt file found in the WebTMA 7 Upgrade Utility package. This file will have the specific error for you to troubleshoot.

Duplicate Data Error

Example 1 -Without Parent Foreign Keys

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of UNIQUE KEY constraint 'UQ_doct_code'. Cannot insert duplicate key in object 'dbo.f_documentType'. The duplicate key value is (Email).".

1. Note the table name: dbo.f_documentType; establish a duplicate check with this table based on the Unique, Non-Clustered index associated with this table: UQ_doct_code.  This SQL should be run on your WebTMA 5 Source Database. This is a template of SQL to use while determining the duplicate entries:

;WITH CTE AS (

SELECT doct_code FROM f_documentType

GROUP BY doct_code HAVING COUNT(*) > 1

)

SELECT B.doct_pk, CTE.doct_code [DuplicateEntry], ROW_NUMBER() OVER(PARTITION BY CTE.doct_code ORDER BY CTE.doct_code) [RowCount]

FROM CTE

INNER JOIN f_documentType [B] ON B.doct_code = CTE.doct_code

1. This statement can be altered to provide duplicate data based on whatever table and unique key constraint you have by altering the entry in the following highlighted portions:

;WITH CTE AS (

SELECT [Indicated Duplicate from Error Message] FROM [Indicated Table from Error Message]

GROUP BY [Indicated Duplicate from Error Message] HAVING COUNT(*) > 1

)

SELECT B.[Table Primary Key Value], CTE.[Indicated Duplicate from Error Message] [DuplicateEntry], ROW_NUMBER() OVER(PARTITION BY CTE.[Indicated Duplicate from Error Message] ORDER BY CTE.[Indicated Duplicate from Error Message]) [RowCount]

FROM CTE

INNER JOIN [Indicated Table from Error Message] [B] ON B.[Indicated Duplicate from Error Message] = CTE.[Indicated Duplicate from Error Message]

1. Generate your UPDATE statement based on your query results:

UPDATE f_documentType SET doct_code = 'Email-1' WHERE doct_pk = 1001

UPDATE f_documentType SET doct_code = 'Email-2' WHERE doct_pk = 1002

UPDATE f_documentType SET doct_code = 'Email-3' WHERE doct_pk = 1003

Please Note: You can alter the duplicate entries to be whatever you want. This is an example of changing them to be unique based on the row count. This will allow you to continue with your data conversion from WebTMA 5 to WebTMA 7. These entries can later be changed to what you want from the front end after you are in WebTMA 7.

Example 2 -With Parent Foreign Keys

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of UNIQUE KEY constraint 'UQ_facilityType_Code'. Cannot insert duplicate key in object 'dbo.f_facilityType. The duplicate key value is (Facility).".

1. Note the table name, dbo.f_facilityType: establish a duplicate check with this table based on the Unique, Non-Clustered index associated with this table: UQ_facilityType_Code. Please note that this index includes a parent key for facility subtypes. This SQL should be run on your WebTMA 5 Source Database. This is a template of SQL to use while determining the duplicate entries:

;WITH CTE AS (

SELECT fdt_code, fdt_parent_fk FROM f_facilityType

GROUP BY fdt_code, fdt_parent_fk HAVING COUNT(*) > 1),

CTE2 AS (SELECT fdt_code FROM f_facilityType WHERE fdt_parent_fk IS NULL

GROUP BY fdt_code HAVING COUNT(*) > 1)

SELECT

 B.fdt_pk,

 CTE.fdt_code [DuplicateEntry],

 CTE.fdt_parent_fk [DuplicateParentEntry],

 ROW_NUMBER() OVER(PARTITION BY CTE.fdt_code, CTE.fdt_parent_fk ORDER BY CTE.fdt_code, CTE.fdt_parent_fk) [RowNumber]

FROM CTE

INNER JOIN f_facilityType [B] ON B.fdt_code = CTE.fdt_code AND B.fdt_parent_fk = CTE.fdt_parent_fk

UNION ALL

SELECT

 B.fdt_pk,

 CTE2.fdt_code [DuplicateEntry],

 NULL,

 ROW_NUMBER() OVER(PARTITION BY CTE2.fdt_code ORDER BY CTE2.fdt_code) [RowNumber]

FROM CTE2

INNER JOIN f_facilityType [B] ON B.fdt_code = CTE2.fdt_code AND B.fdt_parent_fk IS NULL

This statement can be altered to provide a duplicate based on whatever table and unique key constraint you have by altering the entry in the following highlighted portions:

;WITH CTE AS (

SELECT [Indicated Duplicate from Error Message], [Indicated Duplicate Parent FK] FROM [Indicated Table from Error Message]

GROUP BY [Indicated Duplicate from Error Message], [Indicated Duplicate Parent FK] HAVING COUNT(*) > 1),

CTE2 AS (SELECT [Indicated Duplicate from Error Message] FROM [Indicated Table from Error Message] WHERE [Indicated Duplicate Parent FK] IS NULL

GROUP BY [Indicated Duplicate from Error Message]  HAVING COUNT(*) > 1)

SELECT

 B.[Table Primary Key Value],

 CTE.[Indicated Duplicate from Error Message] [DuplicateEntry],

 CTE.[Indicated Duplicate Parent FK] [DuplicateParentEntry],

 ROW_NUMBER() OVER(PARTITION BY CTE.[Indicated Duplicate from Error Message], CTE.[Indicated Duplicate Parent FK] ORDER BY CTE.[Indicated Duplicate from Error Message] , CTE.[Indicated Duplicate Parent FK]) [RowNumber]

FROM CTE

INNER JOIN f_facilityType [B] ON B.[Indicated Duplicate from Error Message] = CTE.[Indicated Duplicate from Error Message] AND B.[Indicated Duplicate Parent FK] = CTE.[Indicated Duplicate Parent FK]

UNION ALL

SELECT

 B.[Table Primary Key Value],

 CTE2.[Indicated Duplicate from Error Message]  [DuplicateEntry],

 NULL,

 ROW_NUMBER() OVER(PARTITION BY CTE2.[Indicated Duplicate from Error Message] ORDER BY CTE2.[Indicated Duplicate from Error Message] ) [RowNumber]

FROM CTE2

INNER JOIN [Indicated Table from Error Message] [B] ON B.[Indicated Duplicate from Error Message]  = CTE2.[Indicated Duplicate from Error Message]  AND B.[Indicated Duplicate Parent FK] IS NULL

1. Generate your UPDATE statement based on your query results:

UPDATE f_facilityType SET fdt_code = 'Facility-1' WHERE fdt_pk = 1006

UPDATE f_facilityType SET fdt_code = 'Facility-2' WHERE fdt_pk = 1007

UPDATE f_facilityType SET fdt_code = 'Facility-1' WHERE fdt_pk = 1003

UPDATE f_facilityType SET fdt_code = 'Facility-2' WHERE fdt_pk = 1005

Please Note: You can alter the duplicate entries to be whatever you want. This is an example of changing them to be unique based on the row count. This will allow you to continue with your data conversion from WebTMA 5 to WebTMA 7. These entries can later be changed to what you want from the front end after you are in WebTMA 7.

Orphaned Data Error

Example 1 - UPDATE Orphaned Record’s Column to NULL

[Execute SQL Task] Error: Executing the query "exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK C..." failed with the following error: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_f_equipment_f_area". The conflict occurred in database "WebTMA7_ExampleDB", table "dbo.f_area", column 'fu_pk'.".  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

1. Notice the two highlighted table names in the error message above. The yellow highlighted table contains a foreign key reference to a non-existent record in the green highlighted table. This should be run on your WebTMA 7 Destination Database. You can view the existing records in the equipment table with the following SELECT statement:

SELECT * FROM f_equipment WHERE NOT EXISTS (SELECT 1 FROM f_area WHERE fu_pk = fm_fu_fk) AND fm_fu_fk IS NOT NULL

Please Note: TMA’s naming conventions for foreign key (FK) constraints is the FK_ + Table that has a column that references a second table + Table that is being referenced by the first table. 

It’s a good practice to denote how many of these records exist and then execute the following statement while verifying the same number of rows have been affected:

UPDATE f_equipment SET fm_fu_fk = NULL WHERE NOT EXISTS (SELECT 1 FROM f_area WHERE fu_pk = fm_fu_fk) AND fm_fu_fk IS NOT NULL

Example 2 - DELETE Record with Orphaned Data

[Execute SQL Task] Error: Executing the query "exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK C..." failed with the following error: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_f_workordertypeRepairCenterLink_f_workordertype". The conflict occurred in database WebTMA7_ExampleDB", table "dbo.f_workorderType", column 'wc_pk'.". Possible failure reasons:

1. You will come across orphaned records whose missing reference cannot be set to NULL. This will require you to DELETE the record. This should be run on your WebTMA 7 Destination Database. You can once again view the offending records in the table with the following SELECT statement:

SELECT * FROM f_workorderTypeRepairCenterLink WHERE NOT EXISTS (SELECT 1 FROM f_workorderType WHERE wc_pk = wcrc_wc_fk) AND wcrc_wc_fk IS NOT NULL

Please Note: TMA’s naming conventions for foreign key (FK) constraints is the FK_ + Table that has a column that references a second table + Table that is being referenced by the first table. 

It’s a good practice to denote how many of these records exist and then execute the following statement while verifying the same number of rows have been affected:

DELETE FROM f_workorderTypeRepairCenterLink WHERE NOT EXISTS (SELECT 1 FROM f_workorderType WHERE wc_pk = wcrc_wc_fk) AND wcrc_wc_fk IS NOT NULL