Arithmetic overflow error

Hi,
Getting below error: "Arithmetic overflow error converting expression to data type int. [SQLSTATE 22003] (Error 8115) " 
While running below query.

CREATE PROCEDURE [dbo].[stp_GroupMailStatHistory_Collect] -- '9/1/2010' -- june  
      -- Add the parameters for the stored procedure here  
   @RunDate DATETIME --Has to be the      
AS  
BEGIN  
      -- SET NOCOUNT ON added to prevent extra result sets from  
      -- interfering with SELECT statements.  
      SET NOCOUNT ON;  
        
      
    DECLARE @StartDate DATETIME  
    DECLARE @EndDate DATETIME  
      
    set @StartDate = DATEADD(month,-1,@RunDate)  
    set @StartDate = CONVERT(VARCHAR(2),MONTH(@StartDate)) + '/1/' + CONVERT(VARCHAR(4),YEAR(@StartDate)) --First day of the month  
    set @EndDate = DATEADD(MONTH,1,@StartDate)   
          
select @StartDate, @EndDate  
    insert      GroupMailStatHistory  
  
    select      @StartDate  
            ,GroupID  
  
            ,GroupName  
  
            ,BillingCompanyID  
  
            ,(select COUNT(1) from archivedb..Acct a (NOLOCK) where a.GroupID = g.GroupID and a.IsActive = 1 and IsArchive = 1 and a.CreateDt < @EndDate) as ArchiveCount  
  
            ,(select COUNT(1) from archivedb..Acct a (NOLOCK) where a.GroupID = g.GroupID and a.IsActive = 1 and IsPersonalAccAllow = 1 and a.CreateDt < @EndDate) as PACount  
  
            ,(select sum(TotalCount) from Reporting..GroupMailStat a (NOLOCK) where a.GroupID = g.GroupID AND a.ProcessDt < @EndDate) as TotalMailCount  
  
            ,(select sum(MsgSizeMB) / 1000.0 as MsgSizeGB from Reporting..GroupMailStat a (NOLOCK) where a.GroupID = g.GroupID AND a.ProcessDt < @EndDate) as TotalMailSize  
              
            , ResellerID  
  
  from      archivedb..[Group] g (NOLOCK)  
  
where      IsActive = 1  
  
END  
  
  
  

Open in new window

VijayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Arithmetic overflow error is when you try to assign a value to a column/variable that is outside of what that data type can hold.  

According to this article an int can hold values from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

Since I don't see any int variables in your proc, I'm guessing that one of your SUM values is going beyond the max value for an int.  Look at the GroupMailStatHistory columns where these SUM values are being inserted, and then verify that they are an int.   Likely you'll have to change them to a bigint.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
Try to adjust column definitions

Try to use

Decimal (16,0)

and

Float

Instead of INT.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Be very careful with your INSERT statement as you're not providing the insert columns, meaning that SQL Server engine might not using the order that you're thinking and provided in the SELECT statement. This can lead the SQL Server engine to execute some internal implicit conversions and may justify the error you're getting.
I would highly recommend you to provide the column list in the INSERT statement and run again to see if the error still persists.
HuaMin ChenProblem resolverCommented:
Vijay,
Is this resolved?
HuaMin ChenProblem resolverCommented:
Details were given to resolve this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
T-SQL

From novice to tech pro — start learning today.