Arithmetic overflow error converting expression to

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 expression to

Author

Topic

devisetti
Starting Member

30 Posts

Posted - 2008-03-28 : 04:54:19

insert into----
select ID_NO,cast(row_number() over(partition by ID_NO order by ID_NO)as varchar(2))
from
test_222

I am trying to insert into test222 table .The id_no column is varchar field
error:
Arithmetic overflow error converting expression to data type varchar.
The statement has been terminated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 04:59:58

Are there more than 99 rows?

select ID_NO, cast(row_number() over(partition by ID_NO order by ID_NO) as varchar(12))
from test_222

E 12°55'05.25"
N 56°04'39.16"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-28 : 05:00:02

increase the size of your varchar(say make it varchar(5)). With your current query, u can generate only 99 row_numbers for each ID_NO

devisetti
Starting Member

30 Posts

Posted - 2008-03-28 : 05:08:47

The maximum I have got in my query is 125.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 05:18:16

It is still to many for VARCHAR(2).

E 12°55'05.25"
N 56°04'39.16"

devisetti
Starting Member

30 Posts

Posted - 2008-03-28 : 05:49:49

String or binary data would be truncated.
This is the error I am getting

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 07:39:55

And we have posted solutions for you to overcome this issue.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 08:16:57

[code]DECLARE @Num INT

SET @Num = 99

SELECT CAST(@Num AS VARCHAR(2)) -- OK

SET @Num = 100

SELECT CAST(@Num AS VARCHAR(2)) -- Neither OK nor fails

DECLARE @Num2 BIGINT

SET @Num2 = 99

SELECT CAST(@Num2 AS VARCHAR(2)) -- OK

SET @Num2 = 100

SELECT CAST(@Num2 AS VARCHAR(2)) -- Fails[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources