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!
Solved
How to delete base model related stuff
- Sidekick
- 22 replies
Best answer by Mark Jongeling
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.
1exec task_delete_tab 'Model', 'Branch', 'exh_log'2exec task_delete_tab 'Model', 'Branch', 'exh_A'3exec task_delete_tab 'Model', 'Branch', 'exh_B'4-- ....
This topic has been closed for replies.
+23Hi 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!
- Sidekick
- 22 replies
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
+23Can 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:
1-- Replace with Model and Branch of your choice2declare @model_id model_id = '{MODEL}', @branch_id branch_id = '{}'34update t15set t1.generated_by_control_proc_id = 'Copied from base model'6from dom t17where t1.generated_by_control_proc_id = 'Copied from base project'8 and t1.model_id = @model_id9 and t1.branch_id = @branch_id1011update t112set t1.generated_by_control_proc_id = 'Copied from base model'13from screen_component_property t114where t1.generated_by_control_proc_id = 'Copied from base project'15 and t1.model_id = @model_id16 and t1.branch_id = @branch_id1718update t119set t1.generated_by_control_proc_id = 'Copied from base model'20from screen_component t121where t1.generated_by_control_proc_id = 'Copied from base project'22 and t1.model_id = @model_id23 and t1.branch_id = @branch_id2425update t126set t1.generated_by_control_proc_id = 'Copied from base model'27from screen_type t128where t1.generated_by_control_proc_id = 'Copied from base project'29 and t1.model_id = @model_id30 and t1.branch_id = @branch_id3132update t133set t1.generated_by_control_proc_id = 'Copied from base model'34from col t135where t1.generated_by_control_proc_id = 'Copied from base project'36 and t1.model_id = @model_id37 and t1.branch_id = @branch_id3839update t140set t1.generated_by_control_proc_id = 'Copied from base model'41from tab t142where t1.generated_by_control_proc_id = 'Copied from base project'43 and t1.model_id = @model_id44 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.
- Sidekick
- 22 replies
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'
+23Hmm, 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:
1declare @model_id model_id = '{MODEL}', @branch_id branch_id = '{BRANCH}'23select t1.tab_id, l.base_model_id, l.base_branch_id4from tab t15join linked_model l6 on l.work_model_id = t1.model_id7 and l.work_branch_id = t1.branch_id8where t1.model_id = @model_id9 and t1.branch_id = @branch_id10 and t1.generated_by_control_proc_id = 'Copied from base model'11 and exists (select 112 from tab b13 where b.model_id = l.base_model_id14 and b.branch_id = l.base_branch_id15 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:
Hide contentShow content
- 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:
1DECLARE @tab_id VARCHAR(50) = 'ext'; -- Replace 'your_tab_id_here' with the tab_id you want to check2declare @model_id model_id = '{model}', @branch_id branch_id = '{branch}'34IF EXISTS (5 SELECT 1 FROM (6 SELECT 'automl_model' AS table_name, tab_id FROM automl_model WHERE model_id = @model_id and branch_id = @branch_id7 UNION ALL SELECT 'automl_model_fitted', tab_id FROM automl_model_fitted WHERE model_id = @model_id and branch_id = @branch_id8 UNION ALL SELECT 'automl_model_predictor', tab_id FROM automl_model_predictor WHERE model_id = @model_id and branch_id = @branch_id9 UNION ALL SELECT 'automl_model_target', tab_id FROM automl_model_target WHERE model_id = @model_id and branch_id = @branch_id10 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_id11 UNION ALL SELECT 'col_data_sensitivity', tab_id FROM col_data_sensitivity WHERE model_id = @model_id and branch_id = @branch_id12 UNION ALL SELECT 'conditional_layout', tab_id FROM conditional_layout WHERE model_id = @model_id and branch_id = @branch_id13 UNION ALL SELECT 'cube', cube_id FROM cube WHERE model_id = @model_id and branch_id = @branch_id14 UNION ALL SELECT 'data_set_tab', tab_id FROM data_set_tab WHERE model_id = @model_id and branch_id = @branch_id15 UNION ALL SELECT 'drag_drop', drag_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id16 UNION ALL SELECT 'drag_drop', drop_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id17 UNION ALL SELECT 'indx', tab_id FROM indx WHERE model_id = @model_id and branch_id = @branch_id18 UNION ALL SELECT 'list_bar_item', tab_id FROM list_bar_item WHERE model_id = @model_id and branch_id = @branch_id19 UNION ALL SELECT 'map_base_layer', tab_id FROM map_base_layer WHERE model_id = @model_id and branch_id = @branch_id20 UNION ALL SELECT 'module_item', tab_id FROM module_item WHERE model_id = @model_id and branch_id = @branch_id21 UNION ALL SELECT 'process_action', tab_id FROM process_action WHERE model_id = @model_id and branch_id = @branch_id22 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_id23 UNION ALL SELECT 'process_action_output_parmtr', tab_id FROM process_action_output_parmtr WHERE model_id = @model_id and branch_id = @branch_id24 UNION ALL SELECT 'process_action_start_tab', tab_id FROM process_action_start_tab WHERE model_id = @model_id and branch_id = @branch_id25 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_id26 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_id27 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_id28 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_id29 UNION ALL SELECT 'role_tab', tab_id FROM role_tab WHERE model_id = @model_id and branch_id = @branch_id30 UNION ALL SELECT 'scheduler', tab_id FROM scheduler WHERE model_id = @model_id and branch_id = @branch_id31 UNION ALL SELECT 'scheduler_view', tab_id FROM scheduler_view WHERE model_id = @model_id and branch_id = @branch_id32 UNION ALL SELECT 'tab_check_constraint', tab_id FROM tab_check_constraint WHERE model_id = @model_id and branch_id = @branch_id33 UNION ALL SELECT 'tab_prefilter', tab_id FROM tab_prefilter WHERE model_id = @model_id and branch_id = @branch_id34 UNION ALL SELECT 'tab_prefilter_grp', tab_id FROM tab_prefilter_grp WHERE model_id = @model_id and branch_id = @branch_id35 UNION ALL SELECT 'tab_prefilter_tag', tab_id FROM tab_prefilter_tag WHERE model_id = @model_id and branch_id = @branch_id36 UNION ALL SELECT 'tab_report', tab_id FROM tab_report WHERE model_id = @model_id and branch_id = @branch_id37 UNION ALL SELECT 'tab_report_grp', tab_id FROM tab_report_grp WHERE model_id = @model_id and branch_id = @branch_id38 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_id39 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_id40 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_id41 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_id42 UNION ALL SELECT 'tab_tag', tab_id FROM tab_tag WHERE model_id = @model_id and branch_id = @branch_id43 UNION ALL SELECT 'tab_task', tab_id FROM tab_task WHERE model_id = @model_id and branch_id = @branch_id44 UNION ALL SELECT 'tab_task_grp', tab_id FROM tab_task_grp WHERE model_id = @model_id and branch_id = @branch_id45 UNION ALL SELECT 'tab_variant', tab_id FROM tab_variant WHERE model_id = @model_id and branch_id = @branch_id46 UNION ALL SELECT 'tab_variant_map_overlay', tab_id FROM tab_variant_map_overlay WHERE model_id = @model_id and branch_id = @branch_id47 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_id48 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_id49 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_id50 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_id51 UNION ALL SELECT 'test_case', tab_id FROM test_case WHERE model_id = @model_id and branch_id = @branch_id52 UNION ALL SELECT 'tile', tab_id FROM tile WHERE model_id = @model_id and branch_id = @branch_id53 ) AS all_tabs54 WHERE tab_id = @tab_id55)56BEGIN57 SELECT 'The tab_id is in use in table: ' + table_name58 FROM (59 SELECT 'automl_model' AS table_name, tab_id FROM automl_model WHERE model_id = @model_id and branch_id = @branch_id60 UNION ALL SELECT 'automl_model_fitted', tab_id FROM automl_model_fitted WHERE model_id = @model_id and branch_id = @branch_id61 UNION ALL SELECT 'automl_model_predictor', tab_id FROM automl_model_predictor WHERE model_id = @model_id and branch_id = @branch_id62 UNION ALL SELECT 'automl_model_target', tab_id FROM automl_model_target WHERE model_id = @model_id and branch_id = @branch_id63 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_id64 UNION ALL SELECT 'col_data_sensitivity', tab_id FROM col_data_sensitivity WHERE model_id = @model_id and branch_id = @branch_id65 UNION ALL SELECT 'conditional_layout', tab_id FROM conditional_layout WHERE model_id = @model_id and branch_id = @branch_id66 UNION ALL SELECT 'cube', cube_id FROM cube WHERE model_id = @model_id and branch_id = @branch_id67 UNION ALL SELECT 'data_set_tab', tab_id FROM data_set_tab WHERE model_id = @model_id and branch_id = @branch_id68 UNION ALL SELECT 'drag_drop', drag_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id69 UNION ALL SELECT 'drag_drop', drop_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id70 UNION ALL SELECT 'indx', tab_id FROM indx WHERE model_id = @model_id and branch_id = @branch_id71 UNION ALL SELECT 'list_bar_item', tab_id FROM list_bar_item WHERE model_id = @model_id and branch_id = @branch_id72 UNION ALL SELECT 'map_base_layer', tab_id FROM map_base_layer WHERE model_id = @model_id and branch_id = @branch_id73 UNION ALL SELECT 'module_item', tab_id FROM module_item WHERE model_id = @model_id and branch_id = @branch_id74 UNION ALL SELECT 'process_action', tab_id FROM process_action WHERE model_id = @model_id and branch_id = @branch_id75 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_id76 UNION ALL SELECT 'process_action_output_parmtr', tab_id FROM process_action_output_parmtr WHERE model_id = @model_id and branch_id = @branch_id77 UNION ALL SELECT 'process_action_start_tab', tab_id FROM process_action_start_tab WHERE model_id = @model_id and branch_id = @branch_id78 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_id79 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_id80 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_id81 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_id82 UNION ALL SELECT 'role_tab', tab_id FROM role_tab WHERE model_id = @model_id and branch_id = @branch_id83 UNION ALL SELECT 'scheduler', tab_id FROM scheduler WHERE model_id = @model_id and branch_id = @branch_id84 UNION ALL SELECT 'scheduler_view', tab_id FROM scheduler_view WHERE model_id = @model_id and branch_id = @branch_id85 UNION ALL SELECT 'tab_check_constraint', tab_id FROM tab_check_constraint WHERE model_id = @model_id and branch_id = @branch_id86 UNION ALL SELECT 'tab_prefilter', tab_id FROM tab_prefilter WHERE model_id = @model_id and branch_id = @branch_id87 UNION ALL SELECT 'tab_prefilter_grp', tab_id FROM tab_prefilter_grp WHERE model_id = @model_id and branch_id = @branch_id88 UNION ALL SELECT 'tab_prefilter_tag', tab_id FROM tab_prefilter_tag WHERE model_id = @model_id and branch_id = @branch_id89 UNION ALL SELECT 'tab_report', tab_id FROM tab_report WHERE model_id = @model_id and branch_id = @branch_id90 UNION ALL SELECT 'tab_report_grp', tab_id FROM tab_report_grp WHERE model_id = @model_id and branch_id = @branch_id91 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_id92 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_id93 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_id94 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_id95 UNION ALL SELECT 'tab_tag', tab_id FROM tab_tag WHERE model_id = @model_id and branch_id = @branch_id96 UNION ALL SELECT 'tab_task', tab_id FROM tab_task WHERE model_id = @model_id and branch_id = @branch_id97 UNION ALL SELECT 'tab_task_grp', tab_id FROM tab_task_grp WHERE model_id = @model_id and branch_id = @branch_id98 UNION ALL SELECT 'tab_variant', tab_id FROM tab_variant WHERE model_id = @model_id and branch_id = @branch_id99 UNION ALL SELECT 'tab_variant_map_overlay', tab_id FROM tab_variant_map_overlay WHERE model_id = @model_id and branch_id = @branch_id100 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_id101 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_id102 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_id103 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_id104 UNION ALL SELECT 'test_case', tab_id FROM test_case WHERE model_id = @model_id and branch_id = @branch_id105 UNION ALL SELECT 'tile', tab_id FROM tile WHERE model_id = @model_id and branch_id = @branch_id106 ) AS used_table107 WHERE tab_id = @tab_id108END109ELSE110BEGIN111 PRINT 'The tab_id is not in use.';112END113
- Sidekick
- 22 replies
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).
+23Hi 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.
1exec task_delete_tab 'Model', 'Branch', 'exh_log'2exec task_delete_tab 'Model', 'Branch', 'exh_A'3exec task_delete_tab 'Model', 'Branch', 'exh_B'4-- ....
- Sidekick
- 22 replies
The tables with the domains are deleted now from the branch.
Thanks!
1 person likes this
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.