x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 6 replies
  • Subscribers 74 subscribers
  • Views 655 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
asasaki

Duplicate case_referenceid

Posted By

Had a weird issue this morning that I can't make sense of.

There were 4 cases created from inbound emails that had duplicate case_referenceid numbers. The original 4 cases were created within a few seconds of each other, and the duplicates started 40 minutes later with the next batch of cases. So it wasn't 8 consecutive cases with duplicate case_referenceids, it was 4 unique case_referenceids followed by the same 4 40 minutes later.

There were no error messages indicating a possible cause in the system, sql or email manager logs, but there are some weird entries in the mail manager log:

2014-11-04 07:16:11.879> mail service count 2
2014-11-04 07:16:17.027> Mailbox: [email protected]
2014-11-04 07:16:17.027> Case tracker start
2014-11-04 07:16:17.089> Mailbox: [email protected]
2014-11-04 07:16:17.089> Create comm
2014-11-04 07:16:17.167> Mailbox: [email protected]
2014-11-04 07:16:17.167> Person has been identified
2014-11-04 07:16:17.292> Mailbox: [email protected]
2014-11-04 07:16:17.292> Case tracker start
2014-11-04 07:16:17.339> Mailbox: [email protected]
2014-11-04 07:16:17.339> Create comm
2014-11-04 07:16:17.448> Mailbox: [email protected]
2014-11-04 07:16:17.448> Person has been identified
2014-11-04 07:16:17.557> Mailbox: [email protected]
2014-11-04 07:16:17.557> Case tracker start
2014-11-04 07:16:17.620> Mailbox: [email protected]
2014-11-04 07:16:17.620> Create comm
2014-11-04 07:16:17.807> Mailbox: [email protected]
2014-11-04 07:16:17.807> Person has been identified
2014-11-04 07:16:17.901> Mailbox: [email protected]
2014-11-04 07:16:17.901> Case tracker start
2014-11-04 07:16:17.901> Mailbox: [email protected]
2014-11-04 07:16:17.901> Create case
2014-11-04 07:16:17.916> Mailbox: [email protected]
2014-11-04 07:16:17.916> Person has been identified: 445229
2014-11-04 07:16:17.994> Mailbox: [email protected]
2014-11-04 07:16:17.994> Create comm
2014-11-04 07:16:18.057> Mailbox: [email protected]
2014-11-04 07:16:18.057> Person has been identified
2014-11-04 07:16:29.897> Mailbox: [email protected]
2014-11-04 07:16:29.897> Case tracker start
2014-11-04 07:16:29.897> Mailbox: [email protected]
2014-11-04 07:16:29.897> Create case
2014-11-04 07:16:29.913> Mailbox: [email protected]
2014-11-04 07:16:29.913> Person has been identified: 445229
2014-11-04 07:16:30.006> Mailbox: [email protected]
2014-11-04 07:16:30.006> Create comm
2014-11-04 07:16:30.053> Mailbox: [email protected]
2014-11-04 07:16:30.053> Person has been identified
2014-11-04 07:16:31.519> Mailbox: [email protected]
2014-11-04 07:16:31.519> Case tracker start
2014-11-04 07:16:31.519> Mailbox: [email protected]
2014-11-04 07:16:31.519> Create case
2014-11-04 07:16:31.535> Mailbox: [email protected]
2014-11-04 07:16:31.535> Person has been identified: 445229
2014-11-04 07:16:31.566> Mailbox: [email protected]
2014-11-04 07:16:31.566> Create comm
2014-11-04 07:16:31.597> Mailbox: [email protected]
2014-11-04 07:16:31.597> Person has been identified
2014-11-04 07:16:32.799> Mailbox: [email protected]
2014-11-04 07:16:32.799> Case tracker start
2014-11-04 07:16:32.799> Mailbox: [email protected]
2014-11-04 07:16:32.799> Create case
2014-11-04 07:16:32.814> Mailbox: [email protected]
2014-11-04 07:16:32.814> Person has been identified: 445229
2014-11-04 07:16:32.845> Mailbox: [email protected]
2014-11-04 07:16:32.845> Create comm
2014-11-04 07:16:32.892> Mailbox: [email protected]
2014-11-04 07:16:32.892> Person has been identified
2014-11-04 07:16:34.093> Sleep Period = 158 seconds
2014-11-04 07:19:15.320> mail service count 2
2014-11-04 07:19:18.533> Mailbox: [email protected]
2014-11-04 07:19:18.533> Case tracker start
2014-11-04 07:19:18.580> Mailbox: [email protected]
2014-11-04 07:19:18.580> Create comm
2014-11-04 07:19:18.627> Mailbox: [email protected]
2014-11-04 07:19:18.627> Person has been identified
2014-11-04 07:19:18.705> Mailbox: [email protected]
2014-11-04 07:19:18.705> Case tracker start
2014-11-04 07:19:18.752> Mailbox: [email protected]
2014-11-04 07:19:18.752> Create comm
2014-11-04 07:19:18.783> Mailbox: [email protected]
2014-11-04 07:19:18.783> Person has been identified
2014-11-04 07:19:18.861> Mailbox: [email protected]
2014-11-04 07:19:18.861> Case tracker start
2014-11-04 07:19:18.908> Mailbox: [email protected]
2014-11-04 07:19:18.908> Create comm
2014-11-04 07:19:18.939> Mailbox: [email protected]
2014-11-04 07:19:18.939> Person has been identified
2014-11-04 07:19:19.032> Mailbox: [email protected]
2014-11-04 07:19:19.032> Case tracker start
2014-11-04 07:19:19.079> Mailbox: [email protected]
2014-11-04 07:19:19.079> Create comm
2014-11-04 07:19:19.110> Mailbox: [email protected]
2014-11-04 07:19:19.110> Person has been identified

Despite all the "create comm" entries at 7:16:17 AM there are only 2 communications created near that time.

Looking through the last 30 days worth of cases, this has happened a few other times, for a total of 7 sets of duplicate case_referenceids. Sometimes it's happened minutes apart, sometimes several hours apart. There is no obvious pattern in the incoming emails that create the cases.

For what it's worth, we're using Sage CRM version 7.2.e.1

Any insight appreciated.

  • 0

    I have seen this too.

    I think this is a locking issue in the DB - several statements at once are trying to update the custom_sysparams (eware_get_reference_id store procedure).

    Some cannot execute because of a lock on the table.

    So then the lock gets released and the statement is then run which updates the value to what it was in the past.

    Perhaps try changing (at your own risk) the store procedure to use "READPAST"

    Ref: aartemiou.blogspot.ie/.../updating-sql-server-tables-without.html

  • 0

    I get locks, and my first thought was along those lines. But recycling the same 4 IDs 40 minutes later didn't seem like typical record lock behavior to me because of the long interval and the 4 sequential ID repeats; I'd normally expect to see multiple repeats of the same ID happening almost at the same time.

    How would record locks cause this behavior?

  • 0

    Anyone have the answer of this issue. I myself is facing this on both version of CRM 7.0 and 7.1

  • 0

    Hi,

    We've previously had issues with duplicates caused by a problem in the code used to grab the next ID in your support.js - it's possible that you've got users logged in, sitting on the New Case screen for a while before saving a new case.

    I don't get how that would cause the same batch for 4 case references to be created, unless we're getting deadlocks or similar. I'd have figured that there wouldn't be any likelihood of all 4 cases being handles as part of the one transaction, and that then being rolled back.

    The solution to the first issue I described would be to increment the next case ref ID before using the reference in a case; the code you'd use for this is below:

    // Get the next ref id

    eWare.ExecSql("UPDATE Custom_SysParams SET Parm_Value = CAST(CAST((CAST(CAST(Parm_Value AS NVARCHAR(20)) AS INT)+1) AS NVARCHAR(20)) AS NTEXT) WHERE Parm_Name = 'CasesCase_CaseId'");

    MySelectQuery = eWare.CreateQueryObj("SELECT Parm_Value FROM Custom_SysParams WHERE Parm_Name = 'CasesCase_CaseId'");

    MySelectQuery.SelectSql();

    if (!MySelectQuery.EOF)

    {

    MyVal = MySelectQuery("Parm_Value");

    Createdby = eWare.GetContextInfo("User", "User_UserID");

    vcase("Case_ReferenceId") = Createdby + "-" + MyVal;

    vcase.SaveChanges();

    MyUpdateQuery=eWare.CreateQueryObj("UPDATE Custom_SysParams SET Parm_Value=\'" + MyVal + "\' WHERE Parm_Name = 'CasesCase_CaseId'");

    MyUpdateQuery.ExecSql();

    }

    You might need to change the UPDATE part to allow for nvarchar(max), if your parm_value columns on custom_sysparams has been updated. I'm mainly posting this for reference - I'm not sure if that's likely to be the case of the first issue posted here.

    On another note: if you're on an old version of CRM, or one that's been upgraded a few times, and you're getting locks on eware_get_reference_id, then you might try this:

    ALTER procedure [dbo].[crm_next_id]

    @table_id int

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @NextID INT

    BEGIN TRY

    SELECT @NextID=0

    UPDATE SQL_Identity set @NEXTID =Id_NextID, Id_NextID = Id_NextID+ 1 WHERE Id_TableId=@table_id

    IF @@RowCount = 1

    RETURN @NextId

    ELSE

    BEGIN

    RAISERROR('NOROW',16,-1)

    END

    END TRY

    BEGIN CATCH

    DECLARE @Msg NVARCHAR(1000)

    SELECT @Msg = ERROR_MESSAGE()

    RAISERROR(@Msg,16,1)

    RETURN 0

    END CATCH

    END

    I'd figure that the first statement (that's blocking the rest of them) is still either working on eware_get_identity_id or crm_next_id. The updates to Custom_Sysparams should just happen in sequence - it wouldn't be locking up the table for extended periods of time.

    That SP was updated a couple of years back; the above should work a little better.

    Hope this helps,

    Rob

  • 0

    Any news on this. I am getting something similar on 7.2 but in my case it is cases generated manually by users in CRM that create the duplicate case refrences - the cases are created over 10 days apart!

    We gave up a while ago using the crm case ref method for email manager cases, because the case reference for these is not required prior to save we just add the reference afterwards using userid+'-'+case_caseid

    For my issue though it seems that on occasions the value in custom_sysparams is set back to a previous number...

  • 0

    Can these codes work with Order and Quote reference Number as well??

    I have a problem on duplicate order and quote reference no.

    I have checked the parm_value as nvarchar(max) already.

    By the way, the sage version is v7.3

*Community Hub is the new name for Sage City