Solved

SQL problem - nvarchar value overflowed an int column

Posted on 2003-12-04
6
4,633 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 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