Failed to ALTER Column

Question

Post reply

Failed to ALTER Column

Jonathan Mallia

SSCertifiable

Points: 5192
More actions
January 15, 2008 at 12:13 am

#69701

Hi guys,
I am trying to increase the size of a numeric field already existing in my table1. Currently it is of type numeric(16, 2).
I executed the command:
ALTER TABLE table1
ALTER COLUMN field1 numeric(16,5) NULL
but I got the error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Anybody please can provide any solution? I am only increasing the size of the field not decreasing (ie truncating) it.
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Is there any data in the table?

If there is any data check for there is any non-numeric value existing the table

JWOL Hall of Fame Points: 3471 More actions · Answer 2

You are only increasing the number of places after the decimal point, not the size of the field. The length remains at 16 which actually reduces the number before the decimal point from 14 to 11. If you look in your table you will have some numbers over 99,999,999,999.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 3

So you are shrinking the value range !?!

So it can nolonger store existing content !

Currently it is of type numeric(16, 2).
I executed the command:
ALTER TABLE table1
ALTER COLUMN field1 numeric(16,5) NULL

Max would be ............. 99999999999999.99

Your new max would be 99999999999.99999

How about.

create table #xyz

(col1 int identity(1,1) primary key,

col2 decimal(16,2) null

)

go

insert into #xyz (col2) values (99999999999999.99)

-- (1 row(s) affected)

go

alter table #xyz

alter column col2 decimal(16,5) null

go

-- Server: Msg 8115, Level 16, State 8, Line 1

-- Arithmetic overflow error converting numeric to data type numeric.

-- The statement has been terminated.

alter table #xyz

alter column col2 decimal(19,5) null

go

--(1 row(s) affected)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 4

Thanks a lot for your replies!

I decided to use the numeric(19, 5) instead of numeric(16, 5)..