No. Series not incrementing

Alex_Chow

Using NAV2009 SP1 RTC. It's an elusive problem that I can never replicate.

Basically, there are times whent he No. Series set on the Sales Order that does not increment properly, causing the user to receive the "Order No. xxx already exist" error.

For people using the RTC, have you entered this problem before?

We do have a process that automatically creates the sales order, but I can't imagine that would cause the problem?

ara3n

To rule out the process that automatically creates Sales orders, Try to use a different Numbering series for it and see if you are still having the issue.

Alex_Chow

ara3n wrote:

To rule out the process that automatically creates Sales orders, Try to use a different Numbering series for it and see if you are still having the issue.

I don't think that's possible in the production environment. I've tried, in vain, different methods to recreate the problem, but couldn't.

I'm thinking that a roll back occured on the number series, or mutliple lock was called on that table and the database couldn't update fast enough?

Is that even possible?

jglathe

Hello Alex,

maybe you could log the timestamps of the GetNextNo/TryGetNextNo calls (and nested call states). This should only marginally affect a production environment. I'd go at it with a log table and OnEntry/OnExit functions

with best regards

Jens

Alex_Chow

jglathe wrote:

Hello Alex,

maybe you could log the timestamps of the GetNextNo/TryGetNextNo calls (and nested call states). This should only marginally affect a production environment. I'd go at it with a log table and OnEntry/OnExit functions

with best regards

Jens

Thanks for the tip. But how would logging the timestamp of these function help me troubleshoot the problem?

Usually, when the user gets this error, they call me right away so I have a sense of when it happens. The problem is they have about 20-30 people entering orders throughout the day, and it happens randomly.

johanna

Dear all,

I have the same issue with NAV 4 SP3..
Still search for the solutions..

Alex_Chow

It think my specific problem is RTC and SQL related. We used the same piece of code prior to the upgrade from 4.0 and this never occured.

Savatage

The only time we see this error "Order No. xxx already exist" is sometimes when we go to Create Sales Order from Lanham EDI module.

We have both "Default Nos." & "Manual Nos." checked.
IF someone types in the next order number instead of F3 the "Last No Used" stays where it is.
So the next time someone F3's or some code is executed to run the No Series that error appears.

Doesn't happen often, most people f3 - but there's always that occasional hiccup.
So the quick fix is Drill into No Series->Drill into "Last No Used" and update it to the actual next available number to be used"

Don't know if it applies here - but I thought I'd share.

jglathe

Hello,

the behaviour on the SQL side seems to be different with RTC. I've traced both the CC and the RTC. Both do a "set transaction isolation level serializable" on the locktable(), at least you can see it in both traces. But the question is when that happens. In the code of CU396:GetNextNo() there is some code between the locktable and the findfirst() which results in a "select * with updlock from No_ Series Line". I would suspect that not every action that results in an SQL statement on CC will result in an SQL statement in the Service Tier. AFAICR the Service Tier does some caching on its own - or at least it seemed this way to me.
I'm afraid that proving this (maybe) bug will be quite difficult. Logging the entry/exit order of the GetNextNo() request would help, but you would still have the problem.

What you can try is verifying that you have the lock on the NoSeriesLine before doing the increment. The idea is this:

- SQL Server automatically places a row lock on a record that is modified or inserted.
- A modify can fail if somebody else was faster. Try again with a locktable, in this case the client waits until
it can place a lock or it times out.
- If we write a unique value into our record, modify it and read it back, we can safely assume we have a row lock. A suitable unique value would be the connection ID of our session.

The code for the change will look like this:

- T309: Add a field named "Connection ID", type BigInteger.
- C396: In function GetNextNo() add two local variables:

L_Session Record Session
L_RetryCount Integer

The code for the changed function is here:

IF SeriesDate = 0D THEN
  SeriesDate := WORKDATE;

IF ModifySeries OR (LastNoSeriesLine."Series Code" = '') THEN BEGIN
  //OS001s os.jgl
  //workaround-try for locking: no explicit lock, try a lock through modify first
  // ***************************************************** OldSchool Solutions *************************************************
  // Original Code
  // IF ModifySeries THEN
  //   NoSeriesLine.LOCKTABLE;
  // ***************************************************** OldSchool Solutions *************************************************
  //OS001e os.jgl
  NoSeries.GET(NoSeriesCode);
  SetNoSeriesLineFilter(NoSeriesLine,NoSeriesCode,SeriesDate);
  IF NOT NoSeriesLine.FINDFIRST THEN BEGIN
    NoSeriesLine.SETRANGE("Starting Date");
    IF NOT NoSeriesLine.ISEMPTY THEN
      ERROR(
        Text004,
        NoSeriesCode,SeriesDate);
    ERROR(
      Text005,
      NoSeriesCode);
  END;
  //OS001s os.jgl
  //all the code above was for selecting the right NoSeriesLine. Now we do the locking through a modify() - 
  //we try again with locktable if it didn't work. We assume that if we can modify and read our Connection ID back,
  //then we have a reliable lock on the NoSeriesLine.
  IF ModifySeries THEN BEGIN
    L_Session.SETRANGE("My Session",TRUE);
    L_Session.FINDFIRST;
    NoSeriesLine.FINDFIRST;
    REPEAT
      NoSeriesLine."Connection ID" := L_Session."Connection ID";
      //this also forces a SERIALIZABLE lock on the line. If we succeed.
      IF NOT NoSeriesLine.MODIFY THEN BEGIN
        //somebody else was faster, try again with SERIALIZABLE lock.
        NoSeriesLine.LOCKTABLE;
        NoSeriesLine.FINDFIRST;
        //we can see who was faster here
        NoSeriesLine."Connection ID" := L_Session."Connection ID";
        NoSeriesLine.MODIFY;
      END;
      NoSeriesLine.FINDFIRST;
      //and - if locking doesn't work - here
      //The RetryCount is just a Sanity check to avoid a closed loop.
      L_RetryCount += 1;
    UNTIL (NoSeriesLine."Connection ID" = L_Session."Connection ID") OR (L_RetryCount > 10);
    IF L_RetryCount > 10 THEN
      ERROR(ErrorNoSeriesLocking,L_Session."Connection ID");
  END;
  //OS001e os.jgl
END ELSE
  NoSeriesLine := LastNoSeriesLine;

IF NoSeries."Date Order" AND (SeriesDate < NoSeriesLine."Last Date Used") THEN
  ERROR(
    Text006,
    NoSeries.Code,NoSeriesLine."Last Date Used");
NoSeriesLine."Last Date Used" := SeriesDate;
IF NoSeriesLine."Last No. Used" = '' THEN BEGIN
  NoSeriesLine.TESTFIELD("Starting No.");
  NoSeriesLine."Last No. Used" := NoSeriesLine."Starting No.";
END ELSE
  IF NoSeriesLine."Increment-by No." <= 1 THEN
    NoSeriesLine."Last No. Used" := INCSTR(NoSeriesLine."Last No. Used")
  ELSE
    IncrementNoText(NoSeriesLine."Last No. Used",NoSeriesLine."Increment-by No.");
IF (NoSeriesLine."Ending No." <> '') AND
   (NoSeriesLine."Last No. Used" > NoSeriesLine."Ending No.")
THEN
  ERROR(
    Text007,
    NoSeriesLine."Ending No.",NoSeriesCode);
IF (NoSeriesLine."Ending No." <> '') AND
   (NoSeriesLine."Warning No." <> '') AND
   (NoSeriesLine."Last No. Used" >= NoSeriesLine."Warning No.") AND
   (NoSeriesCode <> WarningNoSeriesCode) AND
   (TryNoSeriesCode = '')
THEN BEGIN
  WarningNoSeriesCode := NoSeriesCode;
  MESSAGE(
    Text007,
    NoSeriesLine."Ending No.",NoSeriesCode);
END;
NoSeriesLine.VALIDATE(Open);

IF ModifySeries THEN
  NoSeriesLine.MODIFY
ELSE
  LastNoSeriesLine := NoSeriesLine;
EXIT(NoSeriesLine."Last No. Used");

You also need a suitable text constant for "ErrorNoSeriesLocking". To use the session table you need additional rights (select permision) on the dbo.Session view.

I think it's worth a try - doing the modify should force the locking of the row. Since every client does the same, the No. Series should increment as intended.

with best regards

Jens

Alex_Chow

Thanks for the information! I did not know SQL behaves different using RTC vs. Classic client.

I want to report this as a bug to Microsoft but I can't replicated it.

But this is still a problem, a rather annoying problem. It doesn't seem like there's a solution for this other than to wait until Microsoft finds out about this?