Arithmetic overflow error: Msg 8115, Level 16, State 2

COUNT(*) and \”Arithmetic overflow error converting expression to data type int\”
COUNT(*) and \”Arithmetic overflow error converting expression to data type int\”

I’ve got an issue with a stored procedure causing an:

Arithmetic overflow error converting expression to data type int.

The are two tables, the first pulls in data from an ODBC connection. The data contains activities and the times they start. There is a stored procedure which runs daily that converts these start times into intervals.This works the vast majority of the time, but there have been about a dozen days in the last year that this has failed due to the arithmetic overflow error. I’ve attempted to change data types from INT to Numeric, but this hasn’t solved the problem. Any help/thoughts/ideas are greatly appreciated!

The stored procedure:

insert into [DB].[dbo].[Final_Table] ([resourceName] ,[eventdatetime] ,[EV2] ,[IntervalTime] ,[event]) ( SELECT DS1.[resourceName] ,DS2.[eventdatetime] ,DS1.[eventdatetime] as EV2 ,CONVERT(varchar(8), DATEADD(ms, DATEDIFF(SECOND, DS2.[eventdatetime], DS1.[eventdatetime]) * 1000, 0), 114) AS [IntervalTime] ,DS2.[event] FROM [DB].[dbo].[Staging_Table] DS1 INNER JOIN [DB].[dbo].[Staging_Table] DS2 ON DS1.[resourceName] = DS2.[resourceName] AND DS1.[ID] = DS2.[ID] + 1)

The staging table:

CREATE TABLE [dbo].[Staging_Table]( [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [resourceName] [varchar](50) NULL, [eventdatetime] [datetime] NULL, [event] [varchar](9) NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Data in staging table:

The final table:

CREATE TABLE [dbo].[Final_Table]( [resourceName] [varchar](50) NULL, [eventdatetime] [datetime] NULL, [EV2] [datetime] NULL, [IntervalTime] [varchar](8) NULL, [event] [varchar](9) NULL ) ON [PRIMARY] GO

Final Table data:

You are watching: Arithmetic overflow error: Msg 8115, Level 16, State 2. Info created by THVinhTuy selection and synthesis along with other related topics.

Rate this post

Related Posts