SQL server 2005 / SQL server 2008 behaviour versus C/AL Code

Remco

Situation:
We have taken over a customer of another Solution Center.
A codeunit was written to run some jobs from the job scheduler executed by a NAS.
This codeunit contains a loop where fields are updated which are in the current key (The famous Next from Hell). As the example below:

recCust.SETRANGE("Country/Region Code", 'BE');
IF recCust.FINDSET(TRUE) THEN
REPEAT
recCust.VALIDATE("Country/Region Code", 'US');
recCust.MODIFY;
UNTIL recCust.NEXT = 0;

I'm convinced that this code causes problems to execute all the jobs of the job scheduler.

This code is already a few years old. In the past they didn't have that much problems with this piece of code.
Since they have upgraded the SQL server from 2005 to 2008 the problems became more frequent.

My question is if this SQL server 2008 reacts different to this "Next from Hell" than SQL server 2005?

The customer is using a 3.70 database with a 5.0 SP1 client.

geronimo

i generally use marked records and remove the filter after marking them just to stop from having to go through the loop all the time

or replace it with a modifyall in this case

as far as i can tell from experience i havent seen much of a difference in behaviour between the 2 sql versions when working with a database for this kind of loops.

stryk

The SQL Server for sure does not react differently, because it has not the faintest clue about the C/AL code.
What matters is how NAV queries the data, and this is a matter of the NAV Client Version - did you accomplish a "Technical Upgrade" with NAV while upgrading the SQL Server?

But as you said, tgis code sucks; better do the changes on a separate variable:

recCust.SETRANGE("Country/Region Code", 'BE');
IF recCust.FINDSET THEN
  REPEAT
    recCust2.GET(recCust."No.");
    recCust2.VALIDATE("Country/Region Code", 'US');
    recCust2.MODIFY;
 UNTIL recCust.NEXT = 0;

Shedman

I'd say a MODIFYALL would be even better.

recCust.SETRANGE("Country/Region Code", 'BE');
IF NOT recCust.ISEMPTY THEN
    recCust2.MODIFYALL("Country/Region Code", 'US');

And maybe add a SETCURRENTKEY with "Country/Region Code" in it?

DenSter

Shedman wrote:

I'd say a MODIFYALL would be even better.

recCust.SETRANGE("Country/Region Code", 'BE');
IF NOT recCust.ISEMPTY THEN
    recCust2.MODIFYALL("Country/Region Code", 'US');

No that code is going to update ALL customer records, you have to be careful with multiple variables, they may not have the same filters.

Remco

This issue was not about this code. It was just an example that I copied to explain the case.
It is actually about the standard NAV 3.70 code to start the job scheduler.

JobSchedSetup.RESET;
JobSchedSetup.SETCURRENTKEY("Next Check Date","Next Check Time");
IF RunStatus <> JobSchedSetup."Run Status"::"Special Order" THEN
  JobSchedSetup.SETFILTER("Next Check Date",'<>%1',0D);
JobSchedSetup.SETRANGE("Run Status",RunStatus);
JobSchedSetup.SETRANGE(Enabled,TRUE);
IF JobSchedSetup.FIND('-') THEN BEGIN
  REPEAT
    IF RunNow(JobSchedSetup."Next Check Date",JobSchedSetup."Next Check Time") OR
      (JobSchedSetup."Run Status" = JobSchedSetup."Run Status"::"Special Order")
    THEN BEGIN
      JobIdBeingProcessed := JobSchedSetup.ID;
      IF RunObjectFromJobSched.RUN(JobSchedSetup) THEN BEGIN
        JobIdBeingProcessed := '';
        LastJobIdProcessed := JobSchedSetup.ID;
        LastMessage := JobSchedSetup."Message Text";
        JobSchedSetup.GET(JobSchedSetup.ID);
        IF JobSchedSetup."Error Occurred" THEN BEGIN
          ErrorHandling(JobSchedSetup);
          UpdateStatus(JobSchedSetup.ID,JobSchedSetup."Message Text",TRUE,TRUE);
        END ELSE BEGIN
          NewDateAndTime(JobSchedSetup);
          UpdateStatus(JobSchedSetup.ID,JobSchedSetup."Message Text",FALSE,TRUE);
        END;
        COMMIT;
      END ELSE BEGIN
        ErrorHandling(JobSchedSetup);
        UpdateStatus(JobSchedSetup.ID,JobSchedSetup."Message Text",TRUE,TRUE);
        JobIdBeingProcessed := '';
        LastJobIdProcessed := JobSchedSetup.ID;
        LastMessage := JobSchedSetup."Message Text";
        COMMIT;
      END;
    END;
  UNTIL JobSchedSetup.NEXT = 0;
END;

If there was an error in the proces the Runstatus is changed in codeunit RunObjectFromJobSched
Then Navision losses his pointer and the Job Scheduler can skip some jobs.

Microsoft already changed the code in NAV 4.0 by using 2 variables. JobSchedSetup & JobSchedSetup2

JobSchedSetup2.RESET;
JobSchedSetup2.SETCURRENTKEY("Next Check Date","Next Check Time");
JobSchedSetup2.SETFILTER("Next Check Date",'<>%1',0D);
JobSchedSetup2.SETRANGE(
  "Run Status",
  JobSchedSetup2."Run Status"::" ",
  JobSchedSetup2."Run Status"::"With Error");
JobSchedSetup2.SETRANGE(Enabled,TRUE);
IF JobSchedSetup2.FIND('-') THEN BEGIN
  REPEAT
    JobSchedSetup := JobSchedSetup2;
    IF RunNow(
         JobSchedSetup."Next Check Date",
         JobSchedSetup."Next Check Time")
    THEN BEGIN
      IF AllObj.GET(JobSchedSetup."Object Type",JobSchedSetup."Object No.") THEN BEGIN
        IF RunObjectFromJobSched.RUN(JobSchedSetup) THEN BEGIN
          JobSchedSetup.LOCKTABLE;
          NewDateAndTime(JobSchedSetup);
        END ELSE BEGIN
          JobSchedSetup.LOCKTABLE;
          JobSchedSetup."Message Text" := '';
          ErrorHandling(JobSchedSetup);
        END;
      END ELSE BEGIN
        JobSchedSetup.LOCKTABLE;
        JobSchedSetup."Message Text" :=
          STRSUBSTNO(Text020,JobSchedSetup."Object Type",JobSchedSetup."Object No.");
        ErrorHandling(JobSchedSetup);
      END;
      JobSchedSetup.MODIFY;
      UpdateStatus(JobSchedSetup.ID,JobSchedSetup."Message Text",FALSE);
      JobSchedMgtSetup.GET;
      JobSchedMgtSetup."Last Job ID Processed" := JobSchedSetup.ID;
      JobSchedMgtSetup."Last Message" := JobSchedSetup."Message Text";
      JobSchedMgtSetup.MODIFY;
      COMMIT;
    END;
  UNTIL JobSchedSetup2.NEXT = 0;
END;

I assume that this is to prevent the lost of the pointer.
As said earlier our customer runs on a 5.0 SP1 client (not since the migration).
Is it better to use 2 variables or to use FINDSET(TRUE,TRUE)