NAS SQL shceduler, locking trouble

lzr

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;

DenSter

Try a COMMIT before the SLEEP, that should release the locks.

DenSter

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.

lzr

Thanks a lot! Will try it tomorrow morning.

Marije_Brummel

Reading the code it sould not lock because of the FINDSET without locktable.

What code is in the function?

lzr

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;

Waldo

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"?

Marije_Brummel

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.

lzr

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

lzr

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.

Waldo

No, it was just a "hunge" ... but clearly a wrong one .

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

lzr

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?

kine

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"

Waldo

And don't forget to set the "WithEvents" property on the automation variable to "yes".

lzr

There it is, thank you all for your help =D>