Okay so I just saw a question on the Sage Community forum a question about this “issue”and decided I should publish something we did internally for us and a client a while back.
The issue is that somehow, from time to time, you get duplicate case references.
You can check for this right now in your own database…
SQL to detect duplicate Case reference Id values
select Case_ReferenceId as ref, Case_CreatedDate as cd, Case_CreatedBy as cb, *
from Cases
where Case_ReferenceId in (
select case_referenceid
from cases
GROUP BY Case_ReferenceId
HAVING
COUNT(*) > 1)
order by Case_CreatedDate desc
Run this query and chances are you will be surprised to see duplicates.
If you don’t see duplicates stop reading…all is good for you..move on 🙂
Otherwise….
Okay so you can ignore those duplicates or try fix them up… I don’t have a fix for this.
WARNING: If you implement the details below it is at your own risk. This is not something that Sage endorse or even know about.
To stop this happening again however we created a new stored-procedure (SP) to check for where a given reference exists
ct_CheckRefExists
CREATE procedure [dbo].[ct_CheckRefExists]
@refid [nvarchar](128)
AS
BEGIN
SET NOCOUNT ON
DECLARE @bfound int
SELECT @bfound=0 –false
IF EXISTS( select Case_ReferenceId from Cases nolock where Case_ReferenceId =@refid)
BEGIN
set @bfound = 1
END
SET NOCOUNT OFF
RETURN @bfound
END
This SP checks if a ref-id already exists
DECLARE @bfound int
exec @bfound=ct_CheckRefExists @refid=’0-1009d5′
select @bfound
Then we use this within the eware_default_values procedure (see the bold text below…this is what we inserted into the SP)
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+’-‘+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
DECLARE @bfound int
exec @bfound=ct_CheckRefExists @refid=@default_value
while (@bfound=1)
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+’-‘+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
exec @bfound=ct_CheckRefExists @refid=@default_value
END
END
Please note that…
Any CRM updates/upgrades will require that this is run again as the CRM update will overwrite this.