Arithmetic overflow error converting nvarchar to d

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Arithmetic overflow error converting nvarchar to d

Author

Topic

duro
Starting Member

5 Posts

Posted - 2012-10-13 : 13:56:06

hello

when i run the following sql query:

"update table set price = price * 1.1 "

i get the following error :
"Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
The statement has been terminated."

the table is set to nvarchar, and i am just trying to make the prices go up 10%
any ideas?
thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-13 : 14:02:00

Why are you using nvarchar for the price column? Use money instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

duro
Starting Member

5 Posts

Posted - 2012-10-13 : 14:13:40

thank you for your reply almight sql goddess,
when i go into the design view and try and change it to money, then i try and save it, it says "saving changes is not permitted"
any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-13 : 15:48:37

I don't use the design view to make schema changes, can't help you there. I use ALTER TABLE. It'll be something like this, but be sure to check out ALTER TABLE in Books Online and check out the examples.

ALTER TABLE t1 ALTER COLUMN Price money NOT NULL -- or whatever

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

duro
Starting Member

5 Posts

Posted - 2012-10-14 : 04:51:39

when i run

select * from table order by price asc

i get...

115.50
36.00
4.95
4.95
4.95
47.30
49.50
49.50
53.90

when i should get

4.95
4.95
4.95
36.00
47.30
49.50
49.50
53.90
115.50

what do i need to change in the sql query ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 15:14:47

Couple of things:

a) As Tara suggested, the problem (in ordering and in updating) is happening because you have a nvarchar column for the price. That is not a good choice because prices, if I am not mistaken, should always contain numeric values. If you are allowed to change the data type, change it as Tara suggested. If you are on a production server, be very careful - there may be other code that depends on the column being of type nvarchar

When you try to change it in the design view, it is not letting you change it because of a setting. To change this setting, in SSMS, go to Tools-> Options -> Designers -> Table and Database Designers and uncheck the "Prevent saving changes that require table-recreation". HOWEVER, that means, when you make that change, SQL Server is really recreating the table for you. It will do that very gracefully - but still if your table has a lot of records, that can be a scary thing to do, especially on a production server.

b) As to your question about why the prices are not being ordered as you expected - again, this is because the column is of character type. In character ordering, each character starting from the left is looked at. So comparing 115.50 and 36.00, it compares the 1 and 3 and decides that 1 is less than 3, so 115.50 will come before 36.00. Change your ordering to this:

select * from table order by CAST(price AS FLOAT) asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources