Solved

SQL problem - nvarchar value overflowed an int column

Posted on 2003-12-04
6
4,606 Views
Last Modified: 2013-12-24
i am using the following query

SELECT POSITION_LIST.EMPID, REPORTS_TO, USER_PROFILE.EMPID, USER_PROFILE.USERFNAME, USER_PROFILE.USERLNAME
FROM POSITION_LIST LEFT JOIN HR_TOOLBOX_USER_PROFILE ON EMPID = USER_PROFILE.EMPID
WHERE POSITION_LIST.EMPID Is Not Null AND REPORTS_TO in (SELECT POSID
                                    FROM POSITION_LIST
                                    WHERE POSITION_LIST.EMPID=<cfqueryparam value="#UID#">);

which results in
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of the nvarchar value '9261416167' overflowed an int column. Maximum integer value exceeded.

the table POSITION_LIST matches POSID and REPORTS_TO
the table was originally designed in ACCESSS with POSID as a random Autonumber
max value is 2146449165 min value is -2138269668
UID id a 7 digit number 0500000 - 2000000
if i remove the subquery it works fine but i dont get the results i need SQL or CF solutions welcome

TIA

0
Comment
Question by:James Rodgers
  • 3
  • 3
6 Comments
 
LVL 9

Accepted Solution

by:
shooksm earned 500 total points
ID: 9875428
Is this SQL Server 2000?

You could try converting your values to BIGINTs which support values qreater than 2,147,483,647.  Change the following lines:

WHERE POSITION_LIST.EMPID Is Not Null AND REPORTS_TO in (SELECT POSID
                              FROM POSITION_LIST
                              WHERE POSITION_LIST.EMPID=<cfqueryparam value="#UID#">);

To:

WHERE POSITION_LIST.EMPID Is Not Null AND CONVERT(BIGINT, REPORTS_TO) in (SELECT CONVERT(BIGINT, POSID)
                              FROM POSITION_LIST
                              WHERE POSITION_LIST.EMPID=<cfqueryparam value="#UID#">);
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9875478
>>Is this SQL Server 2000?
yes

but i got this error

Type bigint is not a defined system type.
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9875595
Couple things, you can try the CAST function instead:

WHERE POSITION_LIST.EMPID Is Not Null AND CAST(REPORTS_TO AS BIGING) in (SELECT CAST(POSID AS BIGINT)

Also, check to see the what the Database compatibility level is set to.  It should be 80 which is SQL Server 2000.  If it is set to anything less, that will be a problem as the BIGINT datatype was introduced in SQL 2000.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 25

Author Comment

by:James Rodgers
ID: 9875696
>>Is this SQL Server 2000?
i have just been informed that the server is SQL 7 so no bigint datatype

would binary work?

any other solutions, other than changing the datatype in the table?
0
 
LVL 9

Assisted Solution

by:shooksm
shooksm earned 500 total points
ID: 9875751
Trying casting both to a float or real and see if that works.  Also, you could convert the numeric data to a NVARCHAR but you would loose some performance as textual comparision is usually slower than numeric.
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9875783
BINGO...

tried real and it worked
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now