Numeric value out of range. State ID: 22003

afarr

A client is getting the following error in Nav 4.03 (on SQL 2005).

The Following ODBC error occurred
Error: [Microsoft] [ODBC SQL server driver] Numeric value out of range
State ID: 22003

This occurs in the following line (in some customized code, not standard Nav):

PVJobCalculationUnit."Quote price" :=  PVJobCalculationUnit."Price calculated"/SumOfOfferedPrice * BasePrice;

It only comes up while processing one or two documents, in most cases the code is executed without problem.

The fields and variables involved are of type Decimal, and the values involved are about 1,000,000 which is well within the capacity of decimal fields.
I even tried splitting the calculation as follows, to reduce the largest number being handled at any point, but the problem still occurred:

ldec_interim := PVJobCalculationUnit."Price calculated" / SumOfOfferedPrice;
PVJobCalculationUnit."Quote price" :=  ldec_interim * BasePrice;

A colleague suggested that the error might be caused by having too many numbers AFTER (rather than before) the decimal point, but the problem still occurred when I used the ROUND function. (Besides, setting PVJobCalculationUnit."Quote price" to 1/3, i.e. 0.33333333…. did not cause any problems.)

A search online also proved unhelpful, as the only fixes and posts are for Nav 3.7 or before, or for SQL 2000.

Do you have any suggestions about how to resolve this problem?

rhpnt

Install the latest SP for SQL server.

afarr

Thanks for the suggestion.

They already have the latest SQL updates (Windows Update only shows some optional updates, none of which are for SQL; and no high-priority updates).

JosephGress

What are the actual values of "Price Calculated", SumOfOfferedPrice and BasePrice that cause the calculation to go out of range?

I wonder if it goes out of range when it calculates

PVJobCalculationUnit."Price calculated"/SumOfOfferedPrice

I would try changing the calculation to

PVJobCalculationUnit."Quote price" := ( BasePrice * PVJobCalculationUnit."Price calculated" ) / SumOfOfferedPrice

rhpnt

afarr wrote:

Thanks for the suggestion.

They already have the latest SQL updates (Windows Update only shows some optional updates, none of which are for SQL; and no high-priority updates).

I doubt that windows update is enough to be sure the latest version of SQL server is installed. Rather use this link: http://support.microsoft.com/kb/321185/en-us

afarr

You're right about Win Update.

Help - About in SQL SMS shows that they are on build 4035, which is the base version of SQL 2005 SP 3.
According to http://support.microsoft.com/kb/960598 the latest build is 4309, which is Cumulative Update 11 for SP3.

However, these fixes are only available on request, with a disclaimer that:

A hotfix is intended to correct a specific problem.
Apply the hotfix only to systems that are experiencing the specific problem.
Installing the incorrect hotfix can cause damage to your system.
If you are not sure whether the hotfix is the correct one for your system, do not install it.

The specific error mentioned here was not reported or fixed for SQL 2005, and I'm not going to suggest that our client install fixes for several unrelated issues, against the explicit recommendation of MS, even more so when the issue we're trying to fix is very minor.

rhpnt

afarr wrote:

The specific error mentioned here was not reported or fixed for SQL 2005, and I'm not going to suggest that our client install fixes for several unrelated issues, against the explicit recommendation of MS, even more so when the issue we're trying to fix is very minor.

You are right about that...

afarr

JosephGress :

The quantities are:

"Price calculated" 89.2
BasePrice 2,640.75875
SumOfOfferedPrice 3,018.01

I imagine that it would go out of range when multiplying numbers, not when dividing (unless it divides by a quantity less than 1, which is not the case here).
But as you can see, the numbers (even when multiplied) are well below the maximum allowed for decimal fields or variables.

Anyway, I tried your suggestion, and unfortunately it didn't resolve the problem.

I also tried splitting the calculation into two, i.e.

    ldec_interim := PVJobCalculationUnit."Price calculated" * BasePrice;
    PVJobCalculationUnit."Quote price" :=  ldec_interim / SumOfOfferedPrice;

This also did not solve the problem.

dmccrae

Can you confirm that in SQL the type has the correct range.

If you use Management Studio and open the database, tables, find the table name and expand the columns.

The column type should show Decimal(38,20).

afarr

"Quote Price" in the PV Job Calculation Unit table is decimal(38,20).

The value of "Price Calculated" does not change in this calculation, but for what it's worth, it's a FlowField that sums up the Sales Amount field in PV Job Calculation Detail, which is also decimal(38,20).

ldec_interim is a decimal variable in C/SIDE.

afarr

I was almost going to give up on this issue, but I felt stupid telling the client that we just couldn't find a solution, so I spent some more time on it.

I commented out the line of code mentioned in my first post, and the error still came up. The Nav debugger was misleading - the error was actually occurring on a different line, namely

PVJobCalculationUnit.MODIFY;

I should have guessed that an SQL error would come up not when we change the value of some field in Navision, but when we try to write that value to the table.
I found that one of the decimal fields had value 4,477,000,000,000,000,100 which is definitely out of range.

I can now continue debugging as usual. The main lesson to remember (especially if some lines are commented out) is that:
the line indicated by the Nav debugger need not be the exact line being executed .