Solved

SQL problem - nvarchar value overflowed an int column

Posted on 2003-12-04
6
4,646 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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Domino Load Balancer For 2048 Key Requirement 7 47
Htaccess - if subdomain "dev." 2 80
Two nodes for updates and forwarding 8 51
Setting Up Local Lan Web Server 1 64
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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