Sage CRM becomes frozen when using Entity Table Script to update Accpac after upgrading server OS

Hello, I am dealing with a situation that I have been thron into. we currently are running SageCRM 200 V7.0 on a windows 2008 server. We also have AccPac 200 ERP (Version 5.6). We have a Entity Level Table Script in "Company" that is set to upgrade the company address, email or phone numebr in Accpac when ever someone makes a change in CRM. It has been working fine on the Windows 2008 server but we have updated the server to windows 2012 in an effort to eventually upgrade to windows 2019 and since then every time we make a change to a company it causes CRM to freeze. Once we kill the task in SQL and go back to the company we see that it has not been updated but it has in fact been updated in AccPac. we do not see anything in the logs to indicate why this happens. every once in a blue moon we are able to make a change and it does not freeze and is also reflected in CRM and accpac. but once we log out of CRM and try again to starts to freeze again. Any help in tring to track down what may be the culprit would be very helpful. Thank you

Parents
  • 0

    Your SQL statement in the table script is probably locking up. Maybe post an example here and we might see why.

  • 0 in reply to CRMTogether

    Sure, below is what we have. Thanks!

    function InsertRecord() {
    // Handle insert record actions here
    }

    function PostInsertRecord() {
    // Handle post insert record actions here
    }

    function UpdateRecord() {
    // Handle update record actions here
    var commID = 0;
    var custID = 0;
    var msg = "";

    try {
    commID = CRM.GetContextInfo("Company", "Comp_CompanyId");
    custID = CRM.GetContextInfo("Company", "Comp_IdCust");

    if ((commID != null) && (Defined(commID)) && commID > 0 && (custID != null) && (Defined(custID)) && custID > 0) {
    var action = Values("Act");
    var isPromote = false;

    // check if company can be updated (company must be in ACCPAC)
    var errmsg = CustomerUpdate("company");
    if (errmsg == "") {
    switch (action) {
    case "201": // company
    if (ParentEntity == "company" && CustomerUpdate("company") == "") {
    isPromote = true;
    }
    break;
    case "240": // address
    if (checkAddress() == true && CustomerUpdate("address") == "") {
    isPromote = true;
    }
    break;
    case "222": // person
    if (checkPerson() == true && CustomerUpdate("person") == "") {
    isPromote = true;
    }
    break;
    case "182": // email and phone
    if (ParentEntity == "company" && CustomerUpdate("phoneemail") == "") {
    isPromote = true;
    }
    break;
    }
    } else {
    msg = errmsg;
    }

    /*
    msg += "***";
    msg += ", " + commID;
    msg += ", " + custID;
    msg += ", " + ParentEntity;
    msg += ", " + Values("Act");
    msg += ", " + checkAddress();
    msg += ", " + checkPerson();
    msg += ", " + isPromote;
    */

    if (isPromote == true) {
    var result = 1;
    var squery = "EXEC [sage_update_company_crm] " + commID + ", " + CurrentUser.user_logon;
    var Query = CRM.CreateQueryObj(squery);
    Query.SelectSql();
    if (!Query.Eof)
    result = new String(Query.FieldValue("return_value"));
    if (result != "0")
    msg = "Cannot update store information in Enterprise.";
    }
    }
    }
    catch (e) {
    msg = e.message;
    }

    ErrorStr = msg;
    }

    function DeleteRecord() {
    // Handle delete record actions here
    var commID = 0;
    var custID = 0;
    var msg = "";

    try {
    commID = CRM.GetContextInfo("Company", "Comp_CompanyId");
    custID = CRM.GetContextInfo("Company", "Comp_IdCust");

    if ((commID != null) && (Defined(commID)) && commID > 0 && (custID != null) && (Defined(custID)) && custID > 0) {
    // do not allow to delete record if company have not been promoted
    var errmsg = CustomerUpdate("company");
    if (errmsg != "") {
    msg = errmsg;
    }
    }
    }
    catch (e) {
    msg = e.message;
    }

    ErrorStr = msg;
    }

    function Defined(Arg) {
    return (Arg + "" != "undefined");
    }

    function checkPerson() {
    if (((Values("PeLi_TypeAPContact") == "on") || (Values("PeLi_TypeARContact") == "on")) &&
    ((Values("PeLi_TypeAPRemitToContact") == "on") || (Values("PeLi_TypeARShipToContact") == "on"))) {
    return false;
    }

    if ((Values("PeLi_TypeAPContact") == "on") && (Values("PeLi_TypeARContact") == "on")) {
    return false;
    }

    if ((Values("PeLi_TypeAPRemitToContact") == "on") && (Values("PeLi_TypeARShipToContact") == "on")) {
    return false;
    }

    if ((Values("PeLi_TypeARShipToContact") == "on") &&
    ((Pers_IdLocation == null) || !Defined(Pers_IdLocation) || (Pers_IdLocation == ""))) {
    return false;
    }

    if ((Values("PeLi_TypeAPRemitToContact") == "on") &&
    ((Pers_IdLocation == null) || !Defined(Pers_IdLocation) || (Pers_IdLocation == ""))) {
    return false;
    }

    return true;
    }

    function checkAddress() {
    if (((Values("AdLi_TypeAPAddress") == "on") || (Values("AdLi_TypeARAddress") == "on")) &&
    ((Values("AdLi_TypeAPRemitToAddress") == "on") || (Values("AdLi_TypeARShipToAddress") == "on"))) {
    return false;
    }

    if ((Values("AdLi_TypeAPAddress") == "on") && (Values("AdLi_TypeARAddress") == "on")) {
    return false;
    }

    if ((Values("AdLi_TypeAPRemitToAddress") == "on") && (Values("AdLi_TypeARShipToAddress") == "on")) {
    return false;
    }

    if ((Values("AdLi_TypeARShipToAddress") == "on") &&
    ((Addr_IdLocation == null) || !Defined(Addr_IdLocation) || (Addr_IdLocation == ""))) {
    return false;
    }

    if ((Values("AdLi_TypeAPRemitToAddress") == "on") &&
    ((Addr_IdLocation == null) || !Defined(Addr_IdLocation) || (Addr_IdLocation == ""))) {
    return false;
    }

    return true;
    }

  • 0 in reply to Juan P

    What is

    CustomerUpdate

    Looks like it should be a function.

    also whats in

    sage_update_company_crm

    What I siuggest to figure this out is to put a trace into your SQLand then run sql query profiler and see which statement is blocking

    So a trace is

    EG

    select *

    from company where comp_companyid=123 and 999=999

    So  the

    999=999

    is the trace and you can search the profilter output for this

    Another example is

    select *, 'tablescriptx' as sqlorigin

    from company where comp_companyid=123

    so tablescriptx is the name of your script and you put this into all statements called from it.

Reply
  • 0 in reply to Juan P

    What is

    CustomerUpdate

    Looks like it should be a function.

    also whats in

    sage_update_company_crm

    What I siuggest to figure this out is to put a trace into your SQLand then run sql query profiler and see which statement is blocking

    So a trace is

    EG

    select *

    from company where comp_companyid=123 and 999=999

    So  the

    999=999

    is the trace and you can search the profilter output for this

    Another example is

    select *, 'tablescriptx' as sqlorigin

    from company where comp_companyid=123

    so tablescriptx is the name of your script and you put this into all statements called from it.

Children
  • 0 in reply to CRMTogether

    Yes, CustomerUpdate is a Function and SAge_update_company_crm is a script that runs to update our inhouse program. IIt looks like it is getting stuck at "CustomerUpdate" because if we remove everything else and leave that it gets stuck on that function. if we remove that whole part it runs fine. Another wierd thing is how it is able to run all sometimes but if someone else logs in or you log out and back in all of a sudden it cant run. It at least lets us know that it is possible to run this table script. I just need to track down what it blocking it. Thanks again.

  • 0 in reply to Juan P

    My guess is that its a timing issue and sometimes you are lucky and the lock is released from SQL before the statement runs.

    You may have to re-architect to fix this.

  • 0 in reply to CRMTogether

    Sorry if I sound dumb for asking but when you say re-architect, can you explain a little further please? are you talking a reinstall? Thanks