Arithmetic overflow error

Anders_Lidgren

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

Savatage

hopefully this helps
http://msdn2.microsoft.com/en-us/library/ms163363.aspx

Anders_Lidgren

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 ](*,)

ara3n

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.

Anders_Lidgren

I had tried that but no difference. But when I restarted the SQL-server after this adjustment then the problem was gone.

//Anders

ara3n

Hmm looks like it was a sql issue and not navision.

Anders_Lidgren

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

ara3n

there is no info on partner source about this error. Try to run optemize on the sales line table.

DenSter

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_Lidgren

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

I've solved it , 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

kriki

[Topic moved from Navision Attain forum to SQL General forum]