Arithmetic overflow error

Anders_LidgrenAnders_Lidgren Member Posts: 19
edited 2007-10-17 in SQL General
I'm recieveing a 8115,"22003"[Microsoft][ODBC Sql Server Driver][SQL Server]Arithmetic overflow error converting expression to data type numeric when I are trying to Calculate a calcfields that are based on following:

Sum("Sales Line"."Outstanding Qty. (Base)" WHERE (Document Type=CONST(Order),Posting Group=FIELD(Code),Shipment Date=FIELD("Date Filter"),Location Code=FIELD(Locationfilter)))

I've modified the table Sales Line to Not Maintain SIFT-index, and then saved the table. Then I put on Maintain Sift-index again to rebuild the SIFT-tables on the SQL-server but I still have the same problem.

We are running DB 3.10 and 3.70B-client on SQL2K

Anyone who has a clue?

//Andy
Anders Lidgren
Navision Developer since 1998
WM-data Sverige AB

Answers

  • Anders_LidgrenAnders_Lidgren Member Posts: 19
    I can't really see what information in the link that could be applicable on this subject. Because we can't handle the way the SIFT-tables are created and maintained. I've search through the records in the Sales Line table to find extrem values but can't find any



    //Andy ](*,)
    Anders Lidgren
    Navision Developer since 1998
    WM-data Sverige AB
  • ara3nara3n Member Posts: 9,253
    try and disable the sift on sql and let it do a sum on actual table.
    Design the table in navision. View->keys-> Find colum maintain sift. and uncheck that.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Anders_LidgrenAnders_Lidgren Member Posts: 19
    I had tried that but no difference. But when I restarted the SQL-server after this adjustment then the problem was gone.

    //Anders
    Anders Lidgren
    Navision Developer since 1998
    WM-data Sverige AB
  • ara3nara3n Member Posts: 9,253
    Hmm looks like it was a sql issue and not navision.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Anders_LidgrenAnders_Lidgren Member Posts: 19
    The problem is back today ](*,) ..I've tried to sum the field both in Navision and in SQL-server without any problem, but when the Calcfield is calculated in Navision the problem occurs.

    //Anders
    Anders Lidgren
    Navision Developer since 1998
    WM-data Sverige AB
  • ara3nara3n Member Posts: 9,253
    there is no info on partner source about this error. Try to run optemize on the sales line table.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DenSterDenSter Member Posts: 8,304
    Depending on the number of sales lines, you could try disabling MaintainSIFTIndex on all keys and see how well that performs. On the Sales Line table you really should try not to have any sumindexfields.
  • Anders_LidgrenAnders_Lidgren Member Posts: 19
    I know that the sales line table isn't a good table having MaintainSift on, but we have only one key that we maintain Sift-index on all the others are not maintaned. But the problem is altough I set the MaintainSiftIndex to No, and then try to calculate the field I still get the error. ](*,) . The problem can't be in the SIFT-tables or am I wrong?
    Anders Lidgren
    Navision Developer since 1998
    WM-data Sverige AB
  • Anders_LidgrenAnders_Lidgren Member Posts: 19
    I've solved it :D , I deleted the key on Sales Line table and saved the table, then I put the key back on and then it worked. Something had went wrong with the key.

    //Anders
    Anders Lidgren
    Navision Developer since 1998
    WM-data Sverige AB
  • krikikriki Member, Moderator Posts: 9,079
    [Topic moved from Navision Attain forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.