Summary:

Database locking or blocking issues may be seen in copies of Sage CRM integrated with Sage 200. these issues may be caused by the Quote Totals tabel level script on the Quotes entity. The issue can be resolved by amending the tbale level script.



Symptoms:

The issue may arise when synchronising large numbers of quotes from Sage 200 to Sage CRM. Analysis on the database using SQL Server Activity Monitor or the Profiler tool may indicate a block due to a SELECT statement running on the Quotes table.

The following error may appear in the Sage CRM eWare SQL log:

Sep 14 2015 16:04:58.930 6976 10288 1 fqopen,time,sql,errormsg 60497 SELECT * FROM Quotes WHERE Quot_OrderQuoteId = 1234
 Query timeout expired

 

Cause:

An issue with an integrated product was resolved by modifying the Quote Totals table level script on the Quotes entity so that it no longer used the FindRecord function. By default, FindRecord uses the SQL NOLOCK hint. The code used to replace this causes a large number of locks to be placed on the Quotes table in quick succession. This can result in these locks blocking other statements.



Resolution:

Modify the Quote Totals table level script on the Quotes entity so that it uses the NOLOCK hint:

 

function UpdateTotals() {

    // As the quotes can expire, we need to be able to update the totals on the fly. Hence a TLS.
    //var oRec=eWare.FindRecord("Quotes,vQuotes",WhereClause);

    var oRec= CRM.CreateQueryObj('SELECT * FROM Quotes WITH (NOLOCK) WHERE ' + WhereClause);

    oRec.selectSQL();

    if ((!oRec.EOF) && ((oRec("quot_status")=='Expired')||(oRec("quot_status")=='Cancelled'))){

        var OppoID = oRec("quot_opportunityid");
        var OrQuID = oRec("quot_OrderQuoteid");
        var totalColumn =   "quot_GrossAmt";

        if(eWare.SystemOption("CalcNet4OppoTotal") == "Y"){

            totalColumn =   "quot_NettAmt";
        }

        var sql = "Update Opportunity set Oppo_Total = (Select Sum("+totalColumn +") from Quotes WITH (NOLOCK) " +
            " where quot_Status = 'Active' and quot_OrderQuoteid !=" + OrQuID +
            " and quot_OpportunityID =" + OppoID +
            " ) where oppo_OpportunityID = " + OppoID;

        Updatequery = eWare.CreateQueryObj(sql);
        Updatequery.ExecSql();
    }
}

function UpdateRecord() {

    // Handle update record actions here
    UpdateTotals();
}



Status:

This issue has been escalated to the Sage CRM development team, and will be addressed in a future patch for Sage CRM. The workaround  given below will resovle the issue for individual installs.


More information:

In addition to the issue given above, a performance increase may be seen when synchronising large numbers of quotes when the following index is added to the Sage CRM database:


CREATE NONCLUSTERED INDEX [IDX_CUSTOM_Quot_OpportunityId_Status_OrderQuoteId]
ON [dbo].[Quotes] (
       [Quot_opportunityid] ASC,
       [Quot_Status] ASC,
       [Quot_OrderQuoteID] ASC
)
INCLUDE (
       [Quot_Deleted],
       [Quot_grossamt]
)
WITH (
       PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF,
       SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
       ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90
)