‘An item with the same key has already been added’ Message when Opening an Activity (with SQL Scripts to Resolve the Issue)

Background:

Read related article: ‘An item with the same key has already been added’ Message When Opening an Activity.

The error occurs when opening any activities, and only happen to a specific user.

Resolution:

See attached SQL scripts:

/*if you see some results running the Select statement below, it means the duplicated records needs to be deleted */

select u.FirstName + ' ' + u.LastName as Name, m.*, case when t.MaxUserMetaID <> m.ID 
then 1 else 0 end as DeleteMe
from fcUser u
inner join fcRole r on (r.ID = u.RoleID)
inner join fcUserMetaData m on (r.ID = m.UserID)
inner join
(
       select m.UserID RoleID, m.MetaID, Max(m.ID) as MaxUserMetaID
       from fcUserMetaData m
       inner join fcRole r on (m.UserID = r.ID)
       inner join fcUser u on (u.RoleID = r.ID)
       group by m.UserID, m.MetaID
       having count(m.MetaID) > 1
) t on m.UserID = t.RoleID and m.MetaID = t.MetaID
order by m.UserID, m.MetaID

/* run the sql below to delete the duplicated records */

begin transaction

delete from fcUserMetaData where ID IN
(
       select del.ID from
       (
              select u.FirstName + ' ' + u.LastName as Name, m.*, case when t.MaxUserMetaID <> m.ID then 1 else 0 end as DeleteMe
              from fcUser u
              inner join fcRole r on (r.ID = u.RoleID)
              inner join fcUserMetaData m on (r.ID = m.UserID)
              inner join
              (
                     select m.UserID RoleID, m.MetaID, Max(m.ID) as MaxUserMetaID
                     from fcUserMetaData m
                     inner join fcRole r on (m.UserID = r.ID)
                     inner join fcUser u on (u.RoleID = r.ID)
                     group by m.UserID, m.MetaID
                     having count(m.MetaID) > 1
              ) t on m.UserID = t.RoleID and m.MetaID = t.MetaID
       ) del WHERE DeleteMe = 1
)

rollback transaction -- once confirmed, commit transaction by commenting out the 'rollback transaction', and uncomment the 'commit transaction'.
--commit transaction