Skip to Content
0

Arithmetic overflow occurred on Sybase query

Jul 25, 2017 at 01:07 PM

100

avatar image

Hi

This query works on QA, but fails on DEV with "Arithmetic overflow occurred".

SELECT lct_admin('num_logpages', dbid) UsedPages, lct_admin('logsegment_freepages',dbid) FreePages,100.0 - lct_admin('num_logpages', dbid) * 100.0 /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED'

I need to get it working, as it is build into a script that need to execute.

Please advise?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Bret Halford
Jul 25, 2017 at 05:31 PM
1

Hi Willem,

Well, lct_admin() returns integers while the litteral 100.0 will be assigned a numeric(4,1) datatype.

The overflow is probably coming from the "lct_admin('num_logpages', dbid) * 100.0 " part of the equation.

What is the largest value you have for select lct_admin('num_logpages', dbid) from master..sysdatabases?
Would 100 times that amount be larger than MAXINT?

One approach for avoiding the overflow would be to do the division first (resulting in a smaller value) before multiplying by 100.0
Another would be to convert to a datatype with a bigger range. I'd think FLOAT would work well here. Does the following avoid the error?

declare @hundred float
select @hundred = 100.0
SELECT
lct_admin('num_logpages', dbid) UsedPages,
lct_admin('logsegment_freepages',dbid) FreePages,
@hundred - lct_admin('num_logpages', dbid) * @hundred /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree
FROM master..sysdatabases
WHERE name = 'MED'

-bret

Share
10 |10000 characters needed characters left characters exceeded
Willem Lourens Jul 31, 2017 at 10:51 AM
0

Hi Bret

Thanks for your response.

The largest value in "lct_admin('num_logpages', dbid) * 100.0 " is: 4294925009.

I notice this is larger than: "INTEGER A signed 32-bit integer with a range of values between -2147483648 and 2147483647"

The float select statement still fails:

3> declare @hundred float select @hundred = 100.0 SELECT lct_admin('num_logpages', dbid) UsedPages, lct_admin('logsegment_freepages',dbid) FreePages, @hundred - lct_admin('num_logpages', dbid) * @hundred /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED'4> 5> 6> 7> 8> 9> 10> 11> go (1 row affected) Msg 3606, Level 16, State 4: Server 'MED', Line 5: Arithmetic overflow occurred. (0 rows affected)

If I broke up the SELECT script, it fails on this portion:

SELECT 100.0 - lct_admin('num_logpages', dbid) * 100.0 /(lct_admin('logsegment_freepages',dbid) + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED' go

It executes successfully if it executes the division first:

SELECT 100.0 - lct_admin('num_logpages', dbid) /(lct_admin('logsegment_freepages',dbid) / 100.0 + lct_admin('num_logpages', dbid)) PctFree FROM master..sysdatabases WHERE name = 'MED' go

I will have to recommend to have the script updated with this changes.

Share
10 |10000 characters needed characters left characters exceeded