Sometimes we got this error during database synchronization.
Error: Cannot insert duplicate key in object 'dbo.SECURITYROLE'.
Fig#
Solution:
In order to solve this issue, we need to temporarily update all security role tables by SQL:
select * into securityrole_bk from securityrole
update securityrole set recid = recid * 1000
update securityrole set recid = recid * 1000
select * into securitysubrole_bk from securitysubrole
update securitysubrole set securitysubrole = securitysubrole * 1000
update securitysubrole set securityrole = securityrole * 1000
update securitysubrole set securitysubrole = securitysubrole * 1000
update securitysubrole set securityrole = securityrole * 1000
select * into SECURITYROLERUNTIME_bk from SECURITYROLERUNTIME
update SECURITYROLERUNTIME set securityrole = securityrole * 1000
update SECURITYROLERUNTIME set securityrole = securityrole * 1000
select * into securityuserRole_bk from securityuserRole
update securityuserRole set securityrole = securityrole * 1000
update securityuserRole set securityrole = securityrole * 1000
select * into SECURITYROLEEXPLODEDGRAPH_bk from SECURITYROLEEXPLODEDGRAPH
update SECURITYROLEEXPLODEDGRAPH set securitysubrole = securitysubrole * 1000
update SECURITYROLEEXPLODEDGRAPH set securityrole = securityrole * 1000
update SECURITYROLEEXPLODEDGRAPH set securitysubrole = securitysubrole * 1000
update SECURITYROLEEXPLODEDGRAPH set securityrole = securityrole * 1000
select * into SECURITYROLEDUTYEXPLODEDGRAPH_bk from SECURITYROLEDUTYEXPLODEDGRAPH
update SECURITYROLEDUTYEXPLODEDGRAPH set securityrole = securityrole * 1000
update SECURITYROLEDUTYEXPLODEDGRAPH set securityrole = securityrole * 1000
select * into SECURITYROLEPRIVILEGEEXPLODEDGRAPH_bk from SECURITYROLEPRIVILEGEEXPLODEDGRAPH
update SECURITYROLEPRIVILEGEEXPLODEDGRAPH set securityrole = securityrole * 1000
Now, run database synchronization. After Database synchronization succeeded, we will update RecId of SECURITYROLE back to old value by SQL. In order to do that perform the following TSQL to return back the index:
update securityrole set recid = recid / 1000 where recid >= 1000 and recid % 1000 = 0 and not exists (select recid from securityrole as securityrole2 where securityrole2.recid = (securityrole.recid/1000))
update securitysubrole set securitysubrole = securitysubrole / 1000 where securityrole >= 1000 and securityrole % 1000 = 0 and not exists (select securitysubrole from securitysubrole as securitysubrole2 where securitysubrole2.securitysubrole = (securitysubrole.securitysubrole/1000))
update securitysubrole set securityrole = securityrole / 1000 where securityrole >= 1000 and securityrole % 1000 = 0 and not exists (select securityrole from securitysubrole as securitysubrole2 where securitysubrole2.securityrole = (securitysubrole.securityrole/1000))
update SECURITYROLEEXPLODEDGRAPH set securitysubrole = securitysubrole / 1000 where securitysubrole >= 1000 and securitysubrole % 1000 = 0 and not exists (select securitysubrole from SECURITYROLEEXPLODEDGRAPH as SECURITYROLEEXPLODEDGRAPH2 where SECURITYROLEEXPLODEDGRAPH2.securitysubrole= (SECURITYROLEEXPLODEDGRAPH.securitysubrole/1000))
update SECURITYROLEEXPLODEDGRAPH set securityrole = securityrole / 1000 where securityrole >= 1000 and securityrole % 1000 = 0 and not exists (select securityrole from SECURITYROLEEXPLODEDGRAPH as SECURITYROLEEXPLODEDGRAPH2 where SECURITYROLEEXPLODEDGRAPH2.securityrole = (SECURITYROLEEXPLODEDGRAPH.securityrole/1000))
update SECURITYROLERUNTIME set securityrole = securityrole / 1000 where securityrole >= 1000 and securityrole % 1000 = 0 and not exists (select securityrole from SECURITYROLERUNTIME as SECURITYROLERUNTIME2 where SECURITYROLERUNTIME2.securityrole = (SECURITYROLERUNTIME.securityrole/1000))
update securityuserRole set securityrole = securityrole / 1000 where securityrole >= 1000 and securityrole % 1000 = 0 and not exists (select securityrole from securityuserRole as securityuserRole2 where securityuserRole2.securityrole = (securityuserRole.securityrole/1000))
update SECURITYROLEDUTYEXPLODEDGRAPH set securityrole = securityrole / 1000 where securityrole >= 1000 and securityrole % 1000 = 0 and not exists (select securityrole from SECURITYROLEDUTYEXPLODEDGRAPH as SECURITYROLEDUTYEXPLODEDGRAPH2 where SECURITYROLEDUTYEXPLODEDGRAPH2.securityrole = (SECURITYROLEDUTYEXPLODEDGRAPH.securityrole/1000))
update SECURITYROLEPRIVILEGEEXPLODEDGRAPH set securityrole = securityrole / 1000 where securityrole >= 1000 and securityrole % 1000 = 0 and not exists (select securityrole from SECURITYROLEPRIVILEGEEXPLODEDGRAPH as SECURITYROLEPRIVILEGEEXPLODEDGRAPH2 where SECURITYROLEPRIVILEGEEXPLODEDGRAPH2.securityrole = (SECURITYROLEPRIVILEGEEXPLODEDGRAPH.securityrole/1000))
Happy daxing :)
