Error 241

Severity

16

Message text

Scale error during %S_MSG conversion of %s value ’%s’ to a %s field.

Explanation

Error 241 occurs when Adaptive Server attempts to convert a given value from one numeric or decimal datatype to another, but is unable to do so because the scale of the receiving field (the “to” field in the message) is not large enough to accommodate the converted value.

The error is raised during implicit conversions, which occur when Adaptive Server performs certain types of comparisons, and while the server stores and retrieves data. Adaptive Server automatically handles conversions between compatible datatypes, but may be unable to handle the numeric or decimal conversion due to the reasons mentioned above.

For example:

1> declare @numvar numeric(5,2)
2> select @numvar = 38.4434
3> go
Msg 241, Level 16, State 2:
Server ’vin_125_ocstest’, Line 2:
Scale error during implicit conversion of NUMERIC value ’38.4434’ to a NUMERIC field.

The scale of the numeric field numvar (2) is too small to accept the inserted value.

Action

To resolve the error, perform any one of these options. Detailed instructions for each option follow this list.


Setting arithabort off

Use arithabort to direct Adaptive Server not to abort the query due to a numeric truncation error. For example:

1> set arithabort numeric_truncation off
2> go

1> declare @numvar numeric(5,2)
2> select @numvar = 38.44*.72
3> select @numvar
4> go

(1 row affected)
--------
27.67

Using the convert function

Use the general-purpose convert function to specify the output. For example:

1> declare @numvar numeric(5,2)
2> select @numvar = 38.44
3> select @numvar=convert(numeric(5,2),@numvar*.72)
4> select @numvar
5> go
(1 row affected)
(1 row affected)
--------
27.67
(1 row affected)

Declaring a variable’s scale

Declare a large enough scale when defining a variable. For example:

Instead of using:

1> declare @x decimal
2> select @x = 12.345

use:

1> declare @x decimal (5,3)
2> select @x = 12.345

Additional information

The set arithabort and set arithignore options specify behavior following the loss of precision or (for numeric and decimal types) the loss of scale. For more information, see:

When you write SQLJ Java methods (in Adaptive Server Enterprise version 12.5 and later), you can avoid 241 errors by ensuring that input parameters for the function calling the Java method use the proper scale and precision, as described in “Declaring a variable’s scale”.

Versions in which this error is raised

All versions