Forum Discussion

ia01's avatar
ia01
Level 6
12 years ago

Arithmetic overflow error converting expression to data type int

Hi,

 

Just trying to run the following query, it was working for a while, suddenly started getting "Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int." Any suggestion please?

 

select COUNT(*), SUM(SS.ItemSize)/1024 'size in MB' from saveset SS
 
inner join ArchivePoint AP on SS.ArchivePointIdentity = AP.Archivepointidentity
 
inner join gsgs2evfsql01.EnterpriseVaultDirectory.dbo.ArchiveView EVDAV on AP.ArchivePointId = EVDAV.VaultEntryId
where EVDAV.ArchiveName = 'archive'
and SS.ArchivedDate > '2010-10-12'
and SS.ArchivedDate < '2012-12-12'
 
Many Thanks
Enterprise Vault
Information Governance
Tip-How to
  • JesusWept3's avatar
    JesusWept3
    12 years ago

    You're going to have to CAST the item to a bigger int

    SUM(CONVERT(bigint,SS.ItemSize))

5 Replies

Sort By
  • ia01's avatar
    ia01
    Level 6
    12 years ago

    Funny enough if I change the query date range to today's date it works! However i have been using the same last few weeks with 2012-12-12 and it used to work before.

     

    and SS.ArchivedDate > '2010-10-12'
    and SS.ArchivedDate < '2012-10-24'
  • Rob_Wilcox1's avatar
    Rob_Wilcox1
    Level 6
    12 years ago

    My suggestion would be that this:

    SUM(SS.ItemSize)/1024

    isn't returning an integer.

  • JesusWept3's avatar
    JesusWept3
    Level 6
    12 years ago

    You're going to have to CAST the item to a bigger int

    SUM(CONVERT(bigint,SS.ItemSize))

  • JesusWept3's avatar
    JesusWept3
    Level 6
    12 years ago

    The explanation is that an INT can grow to 2,147,483,647 and you are THEN dividing by 1024, so thats basically 2TB it can count up to in a regular INT, a BigInt goes up to 9,223,372,036,854,775,807 which is 8 zettabytes, and I don't think you'll get close to that size at any given point
     

  • ia01's avatar
    ia01
    Level 6
    12 years ago

    Thanks

    SUM(CONVERT(bigint,SS.ItemSize)) worked perfectly as archived Data is just over 2TB now for that particular archive :-)

    Thanks again for your help, really appreciate!