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.

• Set arithabort numeric_truncation to “off.”

• Use the convert function.

• Declare a large enough scale explicitly.

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

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)

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:

• The section “Arithmetic overflow and divide-by-zero errors” in the Transact-SQL User’s Guide

• The chapter “System and User-Defined Datatypes” in Reference Manual: Building Blocks.