[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Error_overflowed an int column

Posted on 2006-03-20
11
Medium Priority
?
1,530 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:matida
  • 6
  • 3
  • 2
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16235640
matida,
> cast('130' as int)
 
U need to cast it as Bigint
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 16235645
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
 
LVL 1

Author Comment

by:matida
ID: 16235741
this returned no matched resullts, just as without the 'N'.  BUT am VERY SURE there are many records for '130'
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16235848
>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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 375 total points
ID: 16236006
SELECT COL1,COL6, COL8
FROM FEB_06
where LEFT(col8,3) = '130'
0
 
LVL 1

Author Comment

by:matida
ID: 16236036
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
 
LVL 1

Author Comment

by:matida
ID: 16236094
ANEESH!
using > where LEFT(col8,3) = '130'
no macthed results at all. this is amazing, coz there are '130' records on the table.
0
 
LVL 1

Author Comment

by:matida
ID: 16236152
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16236201
I guess the table contents comes from some external file?
0
 
LVL 1

Author Comment

by:matida
ID: 16236350
ACTUALLY, the table imported to SQL server from Text, then exported to Access, then re-imported to SQL server.....
0
 
LVL 1

Author Comment

by:matida
ID: 16236381
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question