OpenRowSet - Arithmetic overflow error converting expression to data type int.

Question

OpenRowSet - Arithmetic overflow error converting expression to data type int.

gkramer 23701

Ten Centuries

Points: 1383
More actions
June 18, 2018 at 1:55 am

#406724

Hi,
just wonder why I recieve the following error message
    Msg 8115, Level 16, State 2, Line 2
    Arithmetic overflow error converting expression to data type int.
on this script ( looping trough some servers to retrieve the info per database-file for central storage/reporting )
    select * FROM OPENROWSET('SQLNCLI11','server=<<server>>;trusted_connection=yes',
                    'set fmtonly off
                    SELECT
                    D.Name,
                    F.Name AS FileType,
                    F.physical_name AS PhysicalFile,
                    F.state_desc AS OnlineStatus,
                   CAST(CAST((F.size*8)/1024 as bigint) AS VARCHAR(52)) AS FileSize,
                   CAST(CAST(F.size*8 as bigint) AS varchar(100)) as SizeInBytes,
                    D.Compatibility_level,
                    D.collation_name, D.state_desc, D.snapshot_isolation_state_desc
                FROM sys.master_files F
                    INNER JOIN sys.databases D ON D.database_id = F.database_id
                WHERE
                    D.name not in (''master'',''msdb'',''model'',''tempdb'')
                ORDER BY
                    D.name
                    ')
F.size         = 2034909184     (for one databases on a server - I think this one is causing the error)
F.size*8      = 16279273472
These values are within the range of a BIGINT but still it failes.
Are there limitations on the OPENROWSET and if so - how to bypass them?
Can you please help me out on this (or otherwise I have to re-script the complete stored procudure)
Waiting on your reply,
Guus Kramer
The Netherlands

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

I can't test this, however, I think the problem is the location of your CAST. You need to CAST one of your columns to a BIGINT before calculating your expression, not wrap the whole expression. For example:
The following generates the error: Arithmetic overflow error converting expression to data type int.
SELECT CONVERT(BIGINT,51200 * 102400); However, if you CONVERT one of the values:
SELECT CONVERT(BIGINT,51200) * 102400; The expression evaluates fine.

In your case, this mean you need to change the following:
CAST(CAST((F.size*8)/1024 as bigint) AS VARCHAR(52)) AS FileSize, CAST(CAST(F.size*8 as bigint) AS varchar(100)) as SizeInBytes,To something along the lines of:
CAST((CAST(F.size AS bigint)*8)/1024 AS VARCHAR(52)) AS FileSize, CAST(CAST(F.size as bigint)*8 AS varchar(100)) as SizeInBytes,
The reason for the error is quite simple when you think about it. In my original expression, both 51200 and 102400 are the data type int. When you multiply them together, the result has a value greater than 2^31-1, and the data type is still an int; hence the overflow error. The CAST isn't evaluated till AFTER the expression has been calculated, which is then too late as the error has already occurred.

P.s. On a different note, why are you then converting the values of your numerics to a varchar? This is generally a bad idea.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

gkramer 23701 Ten Centuries Points: 1383 More actions · Answer 2

Thom,

Execent suggesting (not to warp it up at the end but CAST direct on the column to retrieve).
First test is positive !!!

Regarding the VARCHAR part - This due selecting the values in table to create a HTML report.

Guus