Hi,
In the past, we included the base model for the Exchange connector into our work project. But we stopped with that and now we use the ms_graph functionality.
But there are several ‘exh_**’ domains and tables auto generated by the base model, but these can't be deleted from the SF user interface.
Even not after unlinking the base model from the project and generating the definitions.
Is there an option to get them safely deleted from the model?
Thanks in advance!
Page 1 / 1
Hi HJ,
From 2023.2 the deletion of generated objects from base models has changed, the deletion of these objects happens only if they are not in use by other objects.
So the domains will be cleaned up if there are no columns using these. The deletion will happen bottom up so once the lowest level is unused, the rest will be cleaned up when running a Generate definition. You should be able to see the Usage of the domains in the Domains screen in the detail tab Usage:
For the Tables, they could be in use by Process actions for example, or in a Menu. Navigate through the details availabke in Subjects. The important screens here are the Links and the Applied to.
Hope this helps out cleaning it up!
Hi,
I already checked the menu's and process flows and functionality.
But these tables are not used in this branch any more.
But still the tables can't be deleted or are getting deleted by Generate definitions:
And due to this, 41 exh_ domains are not deleted as they are used in the tables above.
I only see that the translations are still there…
We are using the 2023.2.12 version
Can you check the Generated by control procedure value for these tables? In 2023.2, they should be Copied from base model. (See Form > Trace info)
If the value is Copied from base project. then that is most likely the cause of this problem. Fixing that is a little challenging as all related object should be updated as well. If this is the problem, you can try the following and the regenerate the definitions:
-- Replace with Model and Branch of your choice
declare @model_id model_id = '{MODEL}', @branch_id branch_id = '{}'
update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from dom t1
where t1.generated_by_control_proc_id = 'Copied from base project'
and t1.model_id = @model_id
and t1.branch_id = @branch_id
update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from screen_component_property t1
where t1.generated_by_control_proc_id = 'Copied from base project'
and t1.model_id = @model_id
and t1.branch_id = @branch_id
update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from screen_component t1
where t1.generated_by_control_proc_id = 'Copied from base project'
and t1.model_id = @model_id
and t1.branch_id = @branch_id
update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from screen_type t1
where t1.generated_by_control_proc_id = 'Copied from base project'
and t1.model_id = @model_id
and t1.branch_id = @branch_id
update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from col t1
where t1.generated_by_control_proc_id = 'Copied from base project'
and t1.model_id = @model_id
and t1.branch_id = @branch_id
update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from tab t1
where t1.generated_by_control_proc_id = 'Copied from base project'
and t1.model_id = @model_id
and t1.branch_id = @branch_idIf for the given model and branch it gives the desired result, you could opt to run it for all models and branches inside your Software Factory.
In 2023.2, Copied from base project should have been replaced entirely by Copied from base model. This is done during the upgrade to 2023.1.
I've checked, but these are already properly updated with the upgrade.
There are no results in the SF database for the generated_by_control_proc_id = 'Copied from base project'
Hmm, then we need to be looking even deeper, we need to find where the table is used. The code looks through the following tables to see the table (tab_id) is in use:-
- linked_model (not present in any other base model) - This could be the most likely cause
Query to check:
declare @model_id model_id = '{MODEL}', @branch_id branch_id = '{BRANCH}'
select t1.tab_id, l.base_model_id, l.base_branch_id
from tab t1
join linked_model l
on l.work_model_id = t1.model_id
and l.work_branch_id = t1.branch_id
where t1.model_id = @model_id
and t1.branch_id = @branch_id
and t1.generated_by_control_proc_id = 'Copied from base model'
and exists (select 1
from tab b
where b.model_id = l.base_model_id
and b.branch_id = l.base_branch_id
and b.tab_id = t1.tab_id)If this returns a Base model and branch, it means that table is still present in a Base model that is linked to your work model. That's why the table is not removed.
or else:
- automl_model
- automl_model_fitted
- automl_model_predictor
- automl_model_target
- col (either as tab_id or view_tab_id)
- col_data_sensitivity
- conditional_layout
- cube
- data_set_tab
- drag_drop
- indx
- list_bar_item map
- map_base_layer
- module_item
- process_action
- process_action_col_input_parmtr
- process_action_output_parmtr
- process_action_start_tab
- process_action_start_tab_variant
- process_action_tab_prefilter_input_parmtr
- ref (either source_tab_id or target_tab_id)
- role_tab scheduler
- scheduler_view
- tab_check_constraint
- tab_prefilter
- tab_prefilter_grp
- tab_prefilter_tag
- tab_report
- tab_report_grp
- tab_tab_drag_drop (either drag_tab_id or drop_tab_id)
- tab_tab_variant_drag_drop (either drag_tab_id or drop_tab_id) tab_tag
- tab_task tab_task_grp
- tab_variant
- tab_variant_map_overlay
- tab_variant_tab_drag_drop (either drag_tab_id or drop_tab_id)
- tab_variant_tab_variant_drag_drop (either drag_tab_id or drop_tab_id)
- test_case
- tile
Handy query to check thanks to ChatGPT:
DECLARE @tab_id VARCHAR(50) = 'ext'; -- Replace 'your_tab_id_here' with the tab_id you want to check
declare @model_id model_id = '{model}', @branch_id branch_id = '{branch}'
IF EXISTS (
SELECT 1 FROM (
SELECT 'automl_model' AS table_name, tab_id FROM automl_model WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'automl_model_fitted', tab_id FROM automl_model_fitted WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'automl_model_predictor', tab_id FROM automl_model_predictor WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'automl_model_target', tab_id FROM automl_model_target WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'col', tab_id FROM col WHERE (tab_id = @tab_id OR view_tab_id = @tab_id) and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'col_data_sensitivity', tab_id FROM col_data_sensitivity WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'conditional_layout', tab_id FROM conditional_layout WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'cube', cube_id FROM cube WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'data_set_tab', tab_id FROM data_set_tab WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'drag_drop', drag_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'drag_drop', drop_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'indx', tab_id FROM indx WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'list_bar_item', tab_id FROM list_bar_item WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'map_base_layer', tab_id FROM map_base_layer WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'module_item', tab_id FROM module_item WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action', tab_id FROM process_action WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_col_input_parmtr', tab_id FROM process_action_col_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_output_parmtr', tab_id FROM process_action_output_parmtr WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_start_tab', tab_id FROM process_action_start_tab WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_start_tab_variant', tab_id FROM process_action_start_tab_variant WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_tab_prefilter_input_parmtr', tab_id FROM process_action_tab_prefilter_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'ref', source_tab_id AS tab_id FROM ref WHERE source_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'ref', target_tab_id AS tab_id FROM ref WHERE target_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'role_tab', tab_id FROM role_tab WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'scheduler', tab_id FROM scheduler WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'scheduler_view', tab_id FROM scheduler_view WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_check_constraint', tab_id FROM tab_check_constraint WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_prefilter', tab_id FROM tab_prefilter WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_prefilter_grp', tab_id FROM tab_prefilter_grp WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_prefilter_tag', tab_id FROM tab_prefilter_tag WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_report', tab_id FROM tab_report WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_report_grp', tab_id FROM tab_report_grp WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_drag_drop', drag_tab_id FROM tab_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_drag_drop', drop_tab_id FROM tab_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_variant_drag_drop', drag_tab_id FROM tab_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_variant_drag_drop', drop_tab_id FROM tab_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tag', tab_id FROM tab_tag WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_task', tab_id FROM tab_task WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_task_grp', tab_id FROM tab_task_grp WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant', tab_id FROM tab_variant WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_map_overlay', tab_id FROM tab_variant_map_overlay WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_drag_drop', drag_tab_id FROM tab_variant_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_drag_drop', drop_tab_id FROM tab_variant_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drag_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drop_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'test_case', tab_id FROM test_case WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tile', tab_id FROM tile WHERE model_id = @model_id and branch_id = @branch_id
) AS all_tabs
WHERE tab_id = @tab_id
)
BEGIN
SELECT 'The tab_id is in use in table: ' + table_name
FROM (
SELECT 'automl_model' AS table_name, tab_id FROM automl_model WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'automl_model_fitted', tab_id FROM automl_model_fitted WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'automl_model_predictor', tab_id FROM automl_model_predictor WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'automl_model_target', tab_id FROM automl_model_target WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'col', tab_id FROM col WHERE (tab_id = @tab_id OR view_tab_id = @tab_id) and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'col_data_sensitivity', tab_id FROM col_data_sensitivity WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'conditional_layout', tab_id FROM conditional_layout WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'cube', cube_id FROM cube WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'data_set_tab', tab_id FROM data_set_tab WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'drag_drop', drag_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'drag_drop', drop_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'indx', tab_id FROM indx WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'list_bar_item', tab_id FROM list_bar_item WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'map_base_layer', tab_id FROM map_base_layer WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'module_item', tab_id FROM module_item WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action', tab_id FROM process_action WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_col_input_parmtr', tab_id FROM process_action_col_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_output_parmtr', tab_id FROM process_action_output_parmtr WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_start_tab', tab_id FROM process_action_start_tab WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_start_tab_variant', tab_id FROM process_action_start_tab_variant WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'process_action_tab_prefilter_input_parmtr', tab_id FROM process_action_tab_prefilter_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'ref', source_tab_id AS tab_id FROM ref WHERE source_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'ref', target_tab_id AS tab_id FROM ref WHERE target_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'role_tab', tab_id FROM role_tab WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'scheduler', tab_id FROM scheduler WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'scheduler_view', tab_id FROM scheduler_view WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_check_constraint', tab_id FROM tab_check_constraint WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_prefilter', tab_id FROM tab_prefilter WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_prefilter_grp', tab_id FROM tab_prefilter_grp WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_prefilter_tag', tab_id FROM tab_prefilter_tag WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_report', tab_id FROM tab_report WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_report_grp', tab_id FROM tab_report_grp WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_drag_drop', drag_tab_id FROM tab_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_drag_drop', drop_tab_id FROM tab_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_variant_drag_drop', drag_tab_id FROM tab_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tab_variant_drag_drop', drop_tab_id FROM tab_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_tag', tab_id FROM tab_tag WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_task', tab_id FROM tab_task WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_task_grp', tab_id FROM tab_task_grp WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant', tab_id FROM tab_variant WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_map_overlay', tab_id FROM tab_variant_map_overlay WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_drag_drop', drag_tab_id FROM tab_variant_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_drag_drop', drop_tab_id FROM tab_variant_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drag_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drop_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'test_case', tab_id FROM test_case WHERE model_id = @model_id and branch_id = @branch_id
UNION ALL SELECT 'tile', tab_id FROM tile WHERE model_id = @model_id and branch_id = @branch_id
) AS used_table
WHERE tab_id = @tab_id
END
ELSE
BEGIN
PRINT 'The tab_id is not in use.';
END
The first query gives no results in the branch where I deleted the base model from
(DEV), but for TEST it still gives results.
The other query to check for results initially resulted in some roles still having the rights for some of the tables and some data sensitivity setup.
These I could remove.
Now only result is returned that it is in use for the table ‘col’ , but that is logical as it still has columns, (but these cannot be deleted via SF).
Hi HJ, as a final blow you can execute the task_delete_tab via SSMS. Fill in three parameters: Model, Branch, and Table
Then the task will clean up all the tables and underlaying data. That should definitely fix it.
exec task_delete_tab 'Model', 'Branch', 'exh_log'
exec task_delete_tab 'Model', 'Branch', 'exh_A'
exec task_delete_tab 'Model', 'Branch', 'exh_B'
-- ....
The tables with the domains are deleted now from the branch.
Thanks!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.