CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user1342164
user1342164

Reputation: 1454

Stored Procedure Arithmetic overflow error converting expression to data type int

I am trying to use the following stored procedure and I am getting the error

Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25
Arithmetic overflow error converting expression to data type int.

Line 24: dim myadapter as New SqlDataAdapter(mycommand)
Line 25: mycommand.CommandType = CommandType.StoredProcedure
Line 26: myadapter.fill(ds,"results")
Line 27:
Line 28: rs=ds.tables(0).rows(0)

The values in the table aren't 0

Any idea what causes this? 

ALTER PROCEDURE [dbo].[xlaAFSsp_reports]
AS
    SET NOCOUNT ON

    -- Consolidate disk size (also done on xlaAFSsp_expire_files)
    UPDATE xlaAFSstorages 
    SET currentsize = ISNULL((select SUM(filesize) 
                              from xlaAFSfiles 
                              where s3 = 0 
                                and storageid = xlaAFSstorages.storageid), 0)

    create table #ttable (
       totalfiles int,
       usedspace int,
       nonexpiring int,
       protected int,
       totalusers int,
       paidusers int,
       totalstorages int,
       allocatedspace int,
    )

    -- Total Stats
    insert into #ttable (totalfiles, usedspace, nonexpiring, protected, totalusers, paidusers, totalstorages, allocatedspace) 
    values (0, 0, 0, 0, 0, 0, 0, 0)

    update #ttable 
    set totalfiles = (Select count(fileid) from xlaAFSfiles), 
        usedspace = (Select isnull(sum(filesize), 0) from xlaAFSfiles), 
        nonexpiring = (Select count(fileid) from xlaAFSfiles 
                       where fsid in (select fsid from xlaAFSfilesets 
                                      where expiredate = '-')), 
        protected = (Select count(fileid) from xlaAFSfiles 
                     where fsid in (select fsid from xlaAFSfilesets 
                                    where accesspwd <> '')),
        totalusers = (Select COUNT(userid) from xlaAFSusers),
        paidusers = (Select COUNT(userid) from xlaAFSusers where ispaid <> ''),  
        totalstorages = (Select COUNT(storageid) from xlaAFSstorages),
        allocatedspace = (Select isnull(SUM(allocatedsize),-1) 
                          from xlaAFSstorages)


      select * from #ttable
      drop table #ttable

Upvotes: 0

Views: 1600

Answers (1)

Stone
Stone

Reputation: 308

Sounds like one of the sum's is greater than 2,147,483,647.

MS-SQL int, bigint, smallint, and tinyint

Upvotes: 0

Related Questions