NAS SQL shceduler, locking trouble

lzr
lzr Member Posts: 264
edited 2007-04-20 in NAV/Navision Classic Client
I have setup a NAS scheduler on an SQL environment.
Unfortunately the NAS locks all tables, even when it is suppose to sleep (10 min interval). I can't see the problem here. Suggestions are more than welcome.

Here is the code that NAS runs:
WHILE (TRUE) DO
BEGIN
  IF SalesAgent.FINDSET THEN
  REPEAT
    SELECTLATESTVERSION;

    Customer.RESET;
    Customer.CHANGECOMPANY(SalesAgent.Company);
    IF Customer.FINDSET THEN
    REPEAT
      ExportCustCredit(SalesAgent, Customer."No.");
    UNTIL Customer.NEXT = 0;

  UNTIL SalesAgent.NEXT = 0;
  SalesSetup.GET;
  SLEEP(SalesSetup."Export CustCredit Interval" * 1000 * 60);
END;
Navision developer

Answers

  • DenSter
    DenSter Member Posts: 8,307
    Try a COMMIT before the SLEEP, that should release the locks.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • DenSter
    DenSter Member Posts: 8,307
    By the way, consider creating a single instance codeunit, and running a timer in that codeunit, that is a much better approach in my opinion. The timer has an OnTimer event that automatically releases resources at the end of the event.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • lzr
    lzr Member Posts: 264
    Thanks a lot! Will try it tomorrow morning.
    Navision developer
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Reading the code it sould not lock because of the FINDSET without locktable.

    What code is in the function?
    Marije Brummel | Freelance Business Central Specialist
  • lzr
    lzr Member Posts: 264
    It worked with the commit. I will take a look at the ontimer event, haven't used that before.

    Thanks a lot!

    The code in the function is this:
    function ExportCustCredit(SalesAgent, CustomerNo)

Customer.RESET;
Customer.CHANGECOMPANY(SalesAgent.Company);
IF Customer.GET(CustomerNo) THEN
BEGIN

  Customer.SETFILTER("Date Filter", '..%1', WORKDATE);
  Customer.CALCFIELDS("Balance (LCY)", "Balance Due (LCY)");

  CustCredit.INIT;
  CustCredit."Customer No." := Customer."No.";
  CustCredit."Credit Limit" := Customer."Credit Limit (LCY)";
  CustCredit."Remaining Amount" := Customer."Balance (LCY)";
  CustCredit."Amount Passed Due" := Customer."Balance Due (LCY)";
  CustCredit."Sales Agent" := SalesAgent.Code;
  CustCredit.ConvertDate := FORMAT(TODAY);
  CustCredit.ConvertTime := FORMAT(TIME);
  IF NOT CustCredit.INSERT THEN
    CustCredit.MODIFY;

END;
    Navision developer
  • Waldo
    Waldo Member Posts: 3,412
    Indeed, try to use the ontimer ... much better to work with in a NAS.

    Reading the code, it should only lock the CustCredit-table.
    Does it lock also the customer table?

    Is Always Rowlock = TRUE in the "Alter Database"?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    lzr wrote:
    IF NOT CustCredit.INSERT THEN
    CustCredit.MODIFY;

    END;[/code]

    This is not very nice code for SQL. Check your client monitor.

    Better is to check with a GET first.
    Marije Brummel | Freelance Business Central Specialist
  • lzr
    lzr Member Posts: 264
    Mark Brummel wrote:
    lzr wrote:
    IF NOT CustCredit.INSERT THEN
    CustCredit.MODIFY;

    END;[/code]

    This is not very nice code for SQL. Check your client monitor.

    Better is to check with a GET first.

    Ok, thanks for the tip. I have changed it
    Navision developer
  • lzr
    lzr Member Posts: 264
    Waldo wrote:
    Indeed, try to use the ontimer ... much better to work with in a NAS.

    Reading the code, it should only lock the CustCredit-table.
    Does it lock also the customer table?

    Is Always Rowlock = TRUE in the "Alter Database"?

    No it is false. Is this why it locks the whole table?

    Btw do you have any info about how to create this event onTimer for a codeunit? I tried a search but didn't find anything.
    Navision developer
  • Waldo
    Waldo Member Posts: 3,412
    No, it was just a "hunge" ... but clearly a wrong one :wink: .

    Just run your code "OnTimer"- event, and make that your timer runs every "SalesSetup."Export CustCredit Interval" * 1000 * 60" .

    So:
    OnRun:
    SetupYourTimer

    OnTimer:
    RunYourCode

    Usually I do something more:
    OnRun:
    SetupYourTimer

    OnTimer
    Stop_Timer
    RunCode
    Start_Timer

    OnTimer_Error
    HandleError
    Start_Timer

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • lzr
    lzr Member Posts: 264
    Ok, I understand that but I don't see how I can create the onTimer event. I define an automation variable (of what kind?) and make it with events?
    Navision developer
  • kine
    kine Member Posts: 12,562
    lzr wrote:
    Ok, I understand that but I don't see how I can create the onTimer event. I define an automation variable (of what kind?) and make it with events?

    Yes. "'Navision Timer 1.0'.Timer"
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Waldo
    Waldo Member Posts: 3,412
    And don't forget to set the "WithEvents" property on the automation variable to "yes".

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • lzr
    lzr Member Posts: 264
    There it is, thank you all for your help =D>
    Navision developer

Categories