Arithmetic overflow error converting expression to data type float

This blog describes the fixing of the error below:

Research:

It seems the aggregation of the hourly performance tables wend wrong. But what table are we talking about?

Okay looking at the error message the stored procedure what caused the error is PerformanceAggregate . Looking at this procedure you will see the SQL code that is giving the problem below.

SET @Statement =

        'INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' ('

      + '  [DateTime]'

      + ' ,PerformanceRuleInstanceRowId'

      + ' ,ManagedEntityRowId'

      + ' ,SampleCount'

      + ' ,AverageValue'

      + ' ,MinValue'

      + ' ,MaxValue'

      + ' ,StandardDeviation'

      + ')'

      + ' SELECT'

      + '    CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''', 120)'

      + '   ,PerformanceRuleInstanceRowId'

      + '   ,ManagedEntityRowId'

      + '   ,COUNT(*)'

      + '   ,AVG(SampleValue)'

      + '   ,MIN(SampleValue)'

      + '   ,MAX(SampleValue)'

      + '   ,ISNULL(STDEV(SampleValue), 0)'

      + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@CoverViewName)

      + ' WHERE ([DateTime] >= CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''', 120))'

      + '   AND ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''', 120))'

      + ' GROUP BY PerformanceRuleInstanceRowId, ManagedEntityRowId'


Since we are investigating a performance issue the @SchemaName and @CoverViewName would be ‘Perf.vPerfRaw’. Now we have to determine the correct values for the @IntervalStartDateTime and @IntervalEndDateTime. This can be done by looking at the StandardDatasetAggregationHistory table, by running the query below. We know it’s a performance issue so we look at the performance aggregate dataset and then we look in the history table for the last good aggregation for this dataset. 
declare @DataSetId as uniqueidentifier


select top 1 @DataSetId=SDS.DataSetId from dbo.StandardDatasetAggregation SDA


inner join StandardDataSet SDS on SDS.DataSetId=SDA.DataSetId


where SDA.BuildAggregationStoredprocedureName like '%PerformanceAggregate%'


select * from dbo.StandardDatasetAggregationHistory SDA


inner join dbo.StandardDataset SD on SD.DatasetId=SDA.DatasetId


where DirtyInd=1 and SDA.DataSetId=@DataSetId


order by AggregationDateTime ASC


And whala the fist record below gives me the data period caused my error: 
 



  


                       
  




So I change the @IntervalStartDateTime = 2011-09-28 22:00:00  and @IntervalEndDateTime = 2011-09-30 04:01:28. And the query to execute is born: 
SELECT CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 22:00:00', 120), 120) 


,PerformanceRuleInstanceRowId 


,ManagedEntityRowId 


,COUNT(*) 


,AVG(SampleValue) 


,MIN(SampleValue) 


,MAX(SampleValue) 


,ISNULL(STDEV(SampleValue), 0) 


FROM Perf.vPerfRaw 


WHERE ([DateTime] >= CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 21:00:00', 120), 120)) 


AND ([DateTime] < CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 04:01:00', 120), 120)) 


GROUP BY PerformanceRuleInstanceRowId, ManagedEntityRowId 


Hmm but this query’s gives me: 
 
Yes this is exactly what we want. Now we are going to to change the end date to a lower period so we can isolate the record giving the overflow. Doing this I am getting the error period is ‘2011-09-29 21:05:45’ 
So next is to hunt down this bad record: 
SELECT PerformanceRuleInstanceRowId 


,ManagedEntityRowId 


,SampleValue 


FROM Perf.vPerfRaw 


WHERE ([DateTime] = CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 21:05:45', 120), 120)) 


order by SampleValue 



Wooaaw found it: 
 
Hmm the STDEV doesn’t like showing large negative number. 
Let’s look what this function does: 
Returns the statistical standard deviation of all values in the specified expression. May be followed by the OVER clause. 



  


                       
  




Okay we could look and investigate what data value we must change it to but I am not willing to spend to much time. Since the value is soooo large I probably assume the measurement was false. So I will change it to 0. 
I you wanted still to investigate you could use the query below and change the E+217 to a lower value till the query runs okay: 
declare @float as Float 


set @float = -1.1031304526204E+217 


select @float 


select STDEV(@float) 



p.s E+154 is the maximum you can apply ;-)) 
As i said I am going to change this bad records to 0. Since we are looking at a view and this view isn’t updatable we have first to find out the root table containing this data. This isn’t so hard. 
The query below gives you the performance RAW table containing the records: 
The dadasetid is the same as you had got back in the first query as @DataSetId. 
SELECT [StandardDatasetTableMapRowId] 


,[DatasetId] 


,[AggregationTypeId] 


,[TableGuid] 


,[TableNameSuffix] 


,[InsertInd] 


,[OptimizedInd] 


,[StartDateTime] 


,[EndDateTime] 


FROM [OperationsManagerDW].[dbo].[StandardDatasetTableMap] 


where datasetid = '1B1F0F44-A208-4145-8E59-9121357D78F2' 


and [AggregationTypeId] = 0 


and '2011-09-29 21:05:45' between [StartDateTime] and [EndDateTime] 



Running this query will give you below the table we have to change: 
 
Yes yes finally we are there. Now we are going to update the records. The table to use is : Perf.PerfRaw_E721608C35A44620AE3E0DE028C3C5A2 
So the update query is: 



  


                       
  




update Perf.PerfRaw_E721608C35A44620AE3E0DE028C3C5A2 


set SampleValue = 0 


WHERE ([DateTime] = CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 21:05:45', 120), 120)) 


and SampleValue = -1.1031304526204E+217 



The result is , as expected: 
 
Lets check if its now fixed: 
SELECT CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 22:00:00', 120), 120) 


,PerformanceRuleInstanceRowId 


,ManagedEntityRowId 


,COUNT(*) 


,AVG(SampleValue) 


,MIN(SampleValue) 


,MAX(SampleValue) 


,ISNULL(STDEV(SampleValue), 0) 


FROM Perf.vPerfRaw 


WHERE ([DateTime] >= CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 21:05:45', 120), 120)) 


AND ([DateTime] < CONVERT(datetime, + CONVERT(varchar(50), '2011-09-29 21:07:50', 120), 120)) 


GROUP BY PerformanceRuleInstanceRowId, ManagedEntityRowId 



Gives me back: 
 
SO IT’S FIXED !!! 
But what targets workflows caused this bad data. Take the ManagedEntityRowId and PerformanceRuleInstanceRowId data from the bad records. 
Below the query for the guilty targets: 
select * from dbo.ManagedEntity 


where ManagedEntityRowId in (103425,103424,103426) 



 
And the below the query for the related workflows: 



  


                       
  




SELECT PerformanceRule.ObjectName, PerformanceRule.CounterName, PerformanceRuleInstance.InstanceName 


FROM PerformanceRule INNER JOIN 


PerformanceRuleInstance ON PerformanceRule.RuleRowId = PerformanceRuleInstance.RuleRowId 


WHERE (PerformanceRuleInstance.PerformanceRuleInstanceRowId = 346638) 



 
Happy SCOMMING! 
Michel Kamp

DECLARE @sample NUMERIC(5,2)
SET @sample = 1000.554
SELECT @sample

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through the Microsoft SQL for Beginners online course by Brewster Knowlton on Udemy. It’s a great course to start with T-SQL and SQL queries in SQL Server.

DECLARE @sample NUMERIC(5,2)
SET @sample = 100.554 // no rounding because the extra digit is less than 5
SELECT @sample AS Result

Result
100.55

DECLARE @sample NUMERIC(5,2)
SET @sample = 100.555 // rounding will happen
SELECT @sample AS Result

Result
100.56


DECLARE @sample NUMERIC(5,2)
SET @sample = 100.55 // no rounding because value is under defined precision
SELECT @sample AS Result

Result
100.55


DECLARE @sample NUMERIC(5,2)
SET @sample = 100.999 // Rounding to nearest value
SELECT @sample AS Result

Result
101.00

DECLARE @sample NUMERIC(5,2)
SET @sample = 999.999 // error because after rounding value will be
                      // out-of-range for defined numeric type
SELECT @sample AS Result

Result
Arithmetic overflow error converting numeric to data type numeric.

Hi everyone, google hasn’t helped so here I am. I am having some issues with that error message coming up during an update. The update and further description follows. I am fairly certain the issue is with the [lot] columns as the [lot] in lotupdate$ is a float, where as it is an nvarchar in location inventory. I have tried adding a column to the lotupdate$ table as nvarchar and setting lot=to it, but the entry changes from 12345678 to 1.23456e+006 and I have no idea why. when I try to cast lot as nvarchar I get the following error message :

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type nvarchar.

Anyone have any ideas?

update [ils].[dbo].[LOCATION_INVENTORY]

set [ils].[dbo].[LOCATION_INVENTORY].[on_hand_qty]= [dbo].[lotupdate$].[on_hand_qty]

from [ils].[dbo].[LOCATION_INVENTORY] inner join [ils].[dbo].[lotupdate$] on [ils].[dbo].[LOCATION_INVENTORY].[ITEM] = [ils]. [dbo].[lotupdate$].[ITEM]

where [ils].[dbo].[LOCATION_INVENTORY].[LOCATION] = [ils].[dbo].[lotupdate$].[LOCATION]

and [ils].[dbo].[LOCATION_INVENTORY].[lot] = [ils].[dbo].[lotupdate$].[lot]