Very often you come across an issue something like “Cannot insert duplicate key row in object ‘dbo.Phone’ with unique index ‘IDX_Phon_PhoneId”. The issue clearly indicates that the primary id of record which is being inserted already exists. But how is this possible because the primary id of each record which is being inserted in Sage CRM is handled by the system. So how could this situation occur? Let me try and explain how this happens, what should be done and also how Sage CRM generates the next id.
How it Works
It starts with the eware_get_identity_id stored procedure where you need to pass the table name for whic you need the next id. If you edit the stored procedure you will see that performs the folowing steps
1. It gets the next Id from the SQL_Identity table for the specfic entity.
2. It then queries the Rep_Ranges table and fetches the record details again for the specfic table.
3. There are 5 fields in Rep_Ranges table that are used, namely
a. Range_RangeStart
b. Range_RangeEnd
c. Range_NextRangeStart
d. Range_NextRangeEnd
e. Range_Control_NextRange
4. Now, when a new id is generated (in #1) it checks the newly generated with the ranges returned in #3.
5. If the new id is out of the ranges specified i.e. the new id is less than the Range Start or is more than Range End then all the ids within the range have used up and we will have to create a new range.
6. This is a intresting part. Once the system identifies that all the range values are used up it moves ranges up by one step by updating the folowing value i.e.
a. Range_RangeStart > Range_NextRangeStart
b. Range_RangeEnd > Range_NextRangeEnd
c. Range_NextRangeStart > Range_Control_NextRange
d. Range_NextRangeEnd > Range_Control_NextRange + Limit (fixed to 10000)
e. Range_Control_NextRange > Range_Control_NextRange + Limit (fixed to 10000) – 1
7. The new id will now be the first value from the new range
8. The last and important step which system does is to update the SQL_Identity table to so eware_get_next_id will RETURN next id FROM within new range
9. At this point you have id for the new record which is to be inserted.
1. Get the maximum id generated for than entity. This can be done by executing the query Select max(id field) from entity
2. Once you get the max number from #1 you need to query the Rep_Ranges table and check the ranges set for the entity. For instance for Person the query would be Select * from Rep_Ranges where Range_TableId = 13.
3. This is a crucial step. Here you need to compare the max id obtained in #1 with the fields retrieved in #2.
4. If the new id is out of the ranges specified i.e. the new id is less than the Range Start or is more than Range End then all the ids within the range have used up and we will have to create a new range.
5. If the new id is out of the ranges specified i.e. the new id is less than the Range Start or is more than Range End then all the ids within the range have used up and we will have to create a new range.
6. To do this we will have to move ranges up by one step by updating the folowing value i.e.
a. Range_RangeStart > Range_NextRangeStart
b. Range_RangeEnd > Range_NextRangeEnd
c. Range_NextRangeStart > Range_Control_NextRange
d. Range_NextRangeEnd > Range_Control_NextRange + Limit (fixed to 10000)
e. Range_Control_NextRange > Range_Control_NextRange + Limit (fixed to 10000) – 1
Please note that this needs to be done for only the table for which the issue is occurring
7. This process has to be done till the newly created id (in #1) is in the range i.e. between Range Start and range End.
The changes that are mentioned here would touch the Sage CRM metadata and needs to be done carefully and by an expert. Needless to say, please take a backup of the database before making any changes to the tables.
