October 6, 2003 at 11:53 am
I recently upgraded SQL7 to SQL 2000. I've migrated about 50 databases and all are working well except 1. I'm getting and error "Arithmetic overflow error converting numeric to data type numeric". The code was working before I made the upgrade. Anyone have any ideas on what is going on here. Thanks in advance.
Bill
October 6, 2003 at 12:10 pm
Can you post your code and indicate which line generates the error?
October 6, 2003 at 12:41 pm
Thanks for your reply. here it is:
sql = "Select id,activityType,regarding,link,projectName,date,fromEmail,toEmail,subject,body,isRead,adminAlert,custAlert from jobActivity where id = "& request("quoteId") &" order by date desc "
I've narrowed the culprit field to the "quoteId". It's a varchar and I've increased it's length to 8000 to see if that works. It doesn't.
October 6, 2003 at 12:58 pm
What is the data type of column 'id' in table 'jobActivity'?
October 6, 2003 at 1:02 pm
Thanks again for your prompt reply. I really appreciate it.
The data type is varchar with a length of 50
October 6, 2003 at 1:16 pm
quote:
I'm getting and error "Arithmetic overflow error converting numeric to data type numeric".
According to above error, the data in 'id' column should be numeric characters and it looks like some of data are not numeric characters. Can you check that?
October 6, 2003 at 1:38 pm
As far as I can tell the data looks ok.
I am getting a curious thing happening now. I'm copying the database to a backup database and I get an error when copying the tables. This is the error in SQL2000:
Error at Destination for Row 1. Errors encountered so far in this task: 1. Query-based insertion or updating of BLOB values is not supported.
What is this and could it be related to my initial problem.
PS I made the backup database because I wanted to empty the data in the quote table to see if it was the data or the table itself.
Thanks!
Bill
October 7, 2003 at 12:04 am
I have faced a simmillar problem like this, but by reading the mails the solution to problem won't be with the varchar data type I feel.
Check any of your sql code inserts or updates touches either the numeric or decimal data type columns and cross check whether the precision on both target and source tables are same. I give a stress to look at the precision too. If you have defined a difference you will get this sort of error.
Thanks,
Ganesh
October 7, 2003 at 6:52 am
If the query you posted is accurate and id is varchar then I think the problem is with the difference in implicit/explicit conversion between 7 & 2000, you will need quotes around the quotesId data
sql = "Select id,activityType,regarding,link,
projectName,date,fromEmail,toEmail,
subject,body,isRead,adminAlert,custAlert
from jobActivity
where id = '"& request("quoteId") &"' order by date desc "
Edited by - davidburrows on 10/07/2003 06:53:06 AM
Far away is close at hand in the images of elsewhere.
Anon.
October 7, 2003 at 9:54 am
quote:
sql = "Select id,activityType,regarding,link,projectName,date,fromEmail,toEmail,subject,body,isRead,adminAlert,custAlert from jobActivity where id = "& request("quoteId") &" order by date desc "
In this case if let's say the quoteId value is xyz you sql string will be like that:
Select id,activityType,regarding,link,projectName,date,fromEmail,toEmail,subject,body,isRead,adminAlert,custAlert from jobActivity where id = xyz order by date desc
First of all insure that xyz will be between ' signes.
Without that SQ Server is excepting a numeric value.
In SQL2K the " sign is a column name delimiter equivalent to [] (see set quoted delimiter on|off in BOL
The query written by David should work
Bye
Gabor
Bye
Gabor
October 8, 2003 at 5:48 am
I think this error is due to size of the field where u trying to insert data is less than the data.U can eliminate this error by increasing the size of the field where u want to insert.
October 8, 2003 at 6:48 am
Is this issue solved? I have had some overflow errors trying to use convert to convert a 6 digit number (say 100123) to a 5 digit number using "convert(numeric(5,2),intNumber). I don't see any convert functions in your code unless they are in an underlying view or query? If not, I would guess there is an implicit conversion happening and the field in the specific example is too large of a number. Is it possible you have a smallint field or something and are feeding in an integer that exceeds the data size?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply