ASP/SQL: Arithmetic overflow with no overflow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ASP/SQL: Arithmetic overflow with no overflow

Author

Topic

funart
Starting Member

9 Posts

Posted - 2004-06-09 : 13:41:43

Hi There!

I have a stored procedure that is called from an asp page. Every so often, maybe once a month recently, all of a sudden I get an Arithmetic overflow error:


Microsoft OLE DB Provider SQL Server error '80040e57'
(something about an arithmetic overflow because of an int)
(I don't have the actual error message now, b/c it's acctually working now)

However, when I run the procedure from Query Analyzer, I have no problems, the query runs fine, it's only when I run it from ASP. What I found that fixes it, sort of, is to recomiple the procedures (by droping and creating them), rename them, etc. And after changing things a couple of times, it starts to work again.

What I'm asking from anybody here, is this. Have you ever come accross this, or do you have a possible solution, so that it won't happen again?

Thank you very much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-09 : 13:44:32

Are you sure that you aren't sending a value outside of the INT range? If so, you'll need to use BIGINT instead. You can find out what value it is passed by running SQL Profiler. So when you start having the problem again, start a trace in SQL Profiler and determine what value is being passed. You should see an exception in the trace if you've added that event as well.

quote:

int, bigint, smallint, and tinyint
Exact number data types that use integer data.

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

Tara

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-06-09 : 15:33:31

on the asp side of things, you can wrap the form variable (database field) from your stored proc with a CLng() function.
What this does is converts your integer field to long integer (BIGINT) as Tara suggested.
This mostly happens to currency fields and/or fields with decimal points = 1024520.0221

So the syntax will look like this:
CLng(rs(fieldname)) or CLng(request.form("formVariable"))

funart
Starting Member

9 Posts

Posted - 2004-06-16 : 14:28:54

HI there, thanks for your replys.

However, the thing is yes, I am passing in some INT's as parameters, but the largest one is less than 3,000. Like I had mentioned, I tried running the stored procedure from QA, and I even used the exact same parameters, still to get the error.

Thank you very much for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-16 : 14:32:21

Have you tried tracing using SQL Profiler? I suspect that what is being sent to SQL Server is different than what you are expecting to be sent? SQL Profiler will show you everything.

Tara

funart
Starting Member

9 Posts

Posted - 2004-06-17 : 11:09:28

I did check it out with SQL Profiler, and as I recall (as this was over a week ago), what was being passed was the same. However, I will rememebr this for the next time it happens, to keep an eye out for it, just to check it again.

Thanks for your help.

-Eric

funart
Starting Member

9 Posts

Posted - 2004-06-29 : 11:01:10

Hi all!

Well, it's happened to me again. This time I checked out SQL Profiler, and it appears as if the statements are identical. This is what I got. The first is run from the website, the next is from me logging into QA with NT login, and thast is QA with the same login as the website. The worked column is my own, as to whether or not it worked.


Worked | EventClass         | TextData                               | ApplicationName                                | NT | LoginName | CPU  | Reads |Wr.|Duration| CPID |SPID| StartTime 
-------+--------------------+----------------------------------------+------------------------------------------------+----+-----------+------+-------+---+--------+------+----+-------------------------
No     | RPC:Completed      | exec AccountDetails1 2237, 16, 3, 0, 2 | Microsoft(R) Windows (R) 2000 Operating System |    | sa        | 6610 | 34383 | 0 | 7453   | 2080 | 55 | 2004-06-29 10:41:05.813	
Yes    | SQL:BatchCompleted | exec AccountDetails1 2237, 16, 3, 0, 2 | SQL Query Analyzer                             | me | DOMAIN\me | 1156 | 1485  | 0 | 1500   | 1988 | 61 | 2004-06-29 10:41:54.487	
Yes    | SQL:BatchCompleted | exec AccountDetails1 2237, 16, 3, 0, 2 | SQL Query Analyzer                             |    | sa        | 31   | 381   | 0 | 33     | 1988 | 52 | 2004-06-29 10:44:57.133

Any and all help would be greatly appreciated.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 12:04:58

So when you ran it in Query Analyzer, did it get the overflow error? Or did you only get it from the app? If only from the app, then the problem is not with the stored procedure code.

Tara

funart
Starting Member

9 Posts

Posted - 2004-06-29 : 12:19:20

Tara, thanks for your reply.
From QA I did not get the error, it's only from my app (web page).

I know that there shouldn't be a problem with my stored procedure code. It runs well from QA. There is just something going on that the app needs the SP to be recompiled, or something, as that is my only workaround, however undesirable.

And this my friend, is what is leaving me so perplexed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources