It happens on occasion that CRM creates cases with duplicate Case Reference numbers, especially when using SOAP integration and pushing bulk cases to CRM. This can be avoided by making a small change in the Stored Procedure that gets used to generate the reference number in CRM. 

There is a very nice article on how this stored procedure works, what it does and how to modify it by adding a different entity here: https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2009/02/26/the-sage-crm-storedproc-data-type.aspx

This does not cover how we can avoid having duplicate reference numbers. I took a very easy approach as to how to modify the stored procedure to help with this. In my example the code will not increment the number in the system, stored under custom_sysparms, but rather add a number at the end of the ref. You can modify this to add an alphabet letter as well, or if you really want to change the values in custom_sysparms to update the entire system. 

You want to modify the "eware_default_valueseware_default_values" procedure in the related CRM database, and add some code, but we are not adding anything to parameters at all, in fact we are using an "unused" parameter, the "extravalue" parameter. 

the code I am adding is:

while exists(select 1 from cases where Case_ReferenceId = (CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue))
begin
set @extravalue = cast(cast(@extravalue as int)+1 as nvarchar)
end

I am adding that between the "Execute" and "Select" statement per entity. So for each entity/If statement it would look something like this:

IF (@table_name='Cases') AND (@field_name = 'Case_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name

--added for double checking recurring values on the refid
while exists(select 1 from cases where Case_ReferenceId = (CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue))
begin
set @extravalue = cast(cast(@extravalue as int)+1 as nvarchar)
end

SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END

This should then add a number at the end of the Ref number, so if a normal ref is 1-11110 then this will create a ref number as 1-111101. You can also add something like the case_caseid to this as the caseid will ALWAYS be unique, but remeber that the case ref will be very long then, or use the last 2 or 3 digits of the caseid, as this will make it a bit smaller.

Please remember to always test code on your testing/dev system before implementing the code as well as make database backups before implementing any code to production systems.