Tables in our Test Data Manager project are out of order

Document ID : KB000008349
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

It is known that there is a specific order that the tables in this project must be loaded in.  The issue is that there are also known circular references within certain tables.  In the tool when the table order is performed, rather then follow the expected load order, some tables seem to be in alphabetically ordered.  Why does TDM load tables in this manner?  What does TDM use to determine order sequencing?

Environment:
MS SQLTDM
Cause:

If your tables have "circular" references in them, the program will not know the exact order to put them in and will alphabetize them for the order as a default. 

Resolution:

Since there is a known loading order for the tables, a table order via the tool must be performed first to make sure that the sequences are fresh in the backend SQL table(s).  Once this is done, manipulation of the backed SQL table(s) can be done, and the correct sequence can be manually put in.


Here are sample SQL equations to work with based on 2 tables being "out of load order" (please adjust the scripts to meet your specific values): 

SQL RESOLUTION: 

SELECT * FROM 
dbo.gtrep_table_order where rto_proj_id = 2346 and rto_pv_id=2360 order by rto_seq_no 


Based on the above, we opened up SQL server Management Studio, opened up the Select Statement for the gtrep_table_order table. Ordered it by rto_table so the table names can easily be spotted. 

Select [rto_pv_id]
,[rto_table]
,[rto_table_id]
,[rto_proj_id]
,[rto_seq_no]
,[rto_level]
,[rto_tag]
,[rto_type]
,[date_created]
,[who_created]
,[program_created]
,[date_updated]
,[who_updated]
,[program_updated]

from [dbo].[gtrep_table_order] ORDER BY rto_table


The table order (found in rto_seq_no) was swapped for the two tables of interest. An update was performed on each one of the tables and simply changed their sequence number to the proper number. Make sure and commit the changes if needed with your database.

Example:

UPDATE [dbo].[gtrep_table_order]
SET [rto_seq_no] = 25
WHERE [rto_table] = 'SCHED_STASK'


Please make sure and verify the newly introduced table order is correct.