SQL Error_overflowed an int column

SELECT COL1,COL6, COL8
FROM FEB_06
where col8 = cast('130' as int)

if I run this on Query Analyser I get this error:

"Server: Msg 248, Level 16, State 1, Line 1

The conversion of the nvarchar value '  6787681034' overflowed an int column. Maximum integer value exceeded."

COL1, COL6, COL8 are all nvarchar(100) datatype on SQL Server 2000.

I I remove the cast on '130' it retuns zero data matches, am sure tere are many 130 records on te table.
LVL 1
matidaAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT COL1,COL6, COL8
FROM FEB_06
where LEFT(col8,3) = '130'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
matida,
> cast('130' as int)
 
U need to cast it as Bigint
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if col8 is of type nvarchar, your query should really be like this:

SELECT COL1,COL6, COL8
FROM FEB_06
where col8 = N'130'
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
matidaAuthor Commented:
this returned no matched resullts, just as without the 'N'.  BUT am VERY SURE there are many records for '130'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>nvarchar(100)
just to be sure: nvarchar and not nchar(100)

>BUT am VERY SURE there are many records for '130'
can you post some sample data from the table regarding the contents. just for US to be sure...
0
 
matidaAuthor Commented:
ANEESH!
If  I use CAST as Bigint, I get this error :
"Error converting data type nvarchar to bigint"

angelIII!
AM SURE THEY ALL 'NVARCHAR'. below a sample data captured:

COL1, COL2, COL3, COL4, COL6,  COL8,  COL14, COL15, COL16
001-00,      004-00,      02-14-2006 01:32:11,         00:31:56,  4393301, 6787681034, 01:32:05, 01:32:05, 01:32:11
000-00,      004-00,      02-14-2006 09:11:41,00:01:31,   7787757,  4798683782, 09:11:20,09:11:25,      09:11:41

011-00,      004-00,      02-14-2006 09:14:24,      00:00:01,                      130,        44123456789,09:14:11,      09:14:17,      09:14:24

000-00,      004-00,      02-14-2006 09:29:13,      00:04:42,                      7787757,        4798683782,      09:28:54,      09:28:59,      09:29:13
0
 
matidaAuthor Commented:
ANEESH!
using > where LEFT(col8,3) = '130'
no macthed results at all. this is amazing, coz there are '130' records on the table.
0
 
matidaAuthor Commented:
I managed to use this one and it works:

SELECT COL1,COL6, COL8
FROM FEB_06
where LTRIM(COL8) = N'130'

the points are for u both, siince solution is a combetion of ur suggestions
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess the table contents comes from some external file?
0
 
matidaAuthor Commented:
ACTUALLY, the table imported to SQL server from Text, then exported to Access, then re-imported to SQL server.....
0
 
matidaAuthor Commented:
u seem to grab all my points AngelIII! hehehe!! g me some back pls....jusk kidding :-)
am in Gambia, here very very few developers in the country...apart from the net, dificult to get somebody help u out...thanks to expert-exchange lol!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.