Solved

Iam model_sync error on


Userlevel 5
Badge +12

Hi, 

Recently we have added two reports in our app. These work ok, no other reports. 

However, since we added them, our deployment breaks. 

Executing Database Server script 'MODEL_SYNC.sql' - sync_role_tab_variant_report

Error; Cannot insert the value NULL into column 'tab_variant_id', table 'CENTER_ERP_IAM.dbo.role_tab_variant_report'; column does not allow nulls. UPDATE fails.

We use both reports on 1 table, we do not have report variants nor table variants in play here . 

I have applied the rights - several times and checked the settings. Do not see anything weird with it. 

When I comment out the code from the model_sync.sql, all seems good. 

Any thoughts on how this can be fixed? What could be the cause of it? 

Thanks! 

 

Blommetje

 

icon

Best answer by Mark Jongeling 18 January 2024, 11:57

View original

10 replies

Userlevel 7
Badge +22

Hi Blommetje,

This should have been resolved in a hotfix. Can you confirm you ran all available hotfixes for your platform version? (Hotfix released June 16 2023)

Userlevel 5
Badge +12

Hi Mark, 

I’ve applied them now.. but made a version this morning.
We will know on Tuesday now. Will let you know. 

Thanks! 

Blommetje

Userlevel 6
Badge +16

@Blommetje did the hotfix solve the problem?

 

Userlevel 5
Badge +12

Hi Erwin, 

No, it still occurred. Not sure why. I did patch the sf and iam. 

We also applied new right to a report. Perhaps something went wrong.. but alas. 

Any thoughts on how to fix this? 

Blommetje 

Userlevel 5
Badge +12

Made a version yesterday - same issue. 

Patches SF with the hotfixes. 

Any thoughts on how this happens, or what can be set to fix it. Maybe re-set the Roles or something?

Blommetje

Userlevel 7
Badge +22

Hey Blommetje,

We need to determine where the exact cause of the issue lies. The code of the Table-valued function "i_sync_role_tab_variant_report” is responsible for returning the dataset that IAM will receive. Can you verify that the select statements do indeed return correct data and that tab_variant_id has a value for every record?

Code of the TVF; please correct the value for @model_id and @branch_id prior to executing:

declare @model_id         "model_id"  = '[model_name]',
@branch_id "branch_id" = '[branch_name]'

-- Role tab variant report
select r.role_id,
t.tab_id,
t.tab_variant_id,
t.report_id,
t.show_tab_report as granted
from tab_variant_report_overview t
join role r
on r.model_id = t.model_id
and r.branch_id = t.branch_id
join role_tab_report rt
on rt.model_id = t.model_id
and rt.branch_id = t.branch_id
and rt.role_id = r.role_id
and rt.tab_id = t.tab_id
and rt.report_id = t.report_id
join role_report t1
on t1.model_id = t.model_id
and t1.branch_id = t.branch_id
and t1.role_id = r.role_id
and t1.report_id = t.report_id
join role_tab t2
on t2.model_id = t.model_id
and t2.branch_id = t.branch_id
and t2.role_id = r.role_id
and t2.tab_id = t.tab_id
where t.model_id = @model_id
and t.branch_id = @branch_id
and r.all_rights = 0
-- Only sync roles that are included in the selected modules, or else always 1
and dbo.is_sync_role(r.model_id, r.branch_id, r.role_id) = 1
and rt.granted = 1
and t1.granted = 1
and t2.select_granted = 1

union all

-- All rights
select r.role_id,
t.tab_id,
t.tab_variant_id,
t.report_id,
cast(1 as bit) as granted
from tab_variant_report_overview t
join role r
on r.model_id = t.model_id
and r.branch_id = t.branch_id
where t.model_id = @model_id
and t.branch_id = @branch_id
and r.all_rights = 1
and t.show_tab_report = 1
-- Only sync roles that are included in the selected modules, or else always 1
and dbo.is_sync_role(r.model_id, r.branch_id, r.role_id) = 1

 

Userlevel 5
Badge +12

Hi Mark, 

Sure. Ran this on the SF db, and here the results; 

Role_id tab_id tab_variant_Id report_id Granted
Admin project project_not_act_exe planning 1
Admin project wfp_workorder_grid_view planning 1

 

Blommetje

 

Userlevel 7
Badge +22

Looks good to me. The column names are the important part here. 

Next check, in the destination IAM (important)… check the procedure code of sync_role_tab_variant_report

Can you confirm the code is as shown below? Verify there are No capital letters. That would cause an issue.

It should look like this:

Merge statement

and:

Insert new model records

 

Userlevel 5
Badge +8

Hi @Blommetje, did you manage to solve the problem?

Userlevel 5
Badge +12

Yes! 

I reran all the hotfixes and checked this, and not sure exactly but did see a capital. 
Somehow the hotfix wasn’t applied (or rolled back due to a restore or something), but after that it was solved! 

Thanks! 

Blommetje

Reply