I'm doing a simple query like this:
select * from MyTable
This results in the following error:
An error occurred while executing batch. Error message is: Arithmetic Overflow
I can run select top 14 * from MyTable and it works without a problem but on the 15th value the error occurs, select top 15 * from MyTable shows Arithmetic Overflow error.
The table consists of columns with data types: int, float and smallint.
I have confirmed that the error does not occur in the int or smallint columns so it is happening in a float column.
Two questions:
Running Microsoft SQL Server 2005 - 9.00.5057.00 on Microsoft Windows Server 2003 R2 Standard Edition with Service Pack 2.
Update with Design:
USE [myDB]
GO
/****** Object: Table [dbo].[MyTable] Script CMte: 12/10/2019 10:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[DeliveryYearID] [int] IDENTITY(1,1) NOT NULL,
[DeliveryID] [int] NOT NULL,
[DeliveryYear] [int] NOT NULL,
[VolumeDelivery_H] [int] NOT NULL CONSTRAINT [DF__tblSaleOb__Volum__74C75C08] DEFAULT (0),
[VolumeDelivery_S] [int] NOT NULL CONSTRAINT [DF__tblSaleOb__Volum__75BB8041] DEFAULT (0),
[SurchargeSale_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__Surch__76AFA47A] DEFAULT (0),
[SurchargeAdjust_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__Surch__77A3C8B3] DEFAULT (0.0),
[SurchargeSale_moving] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__Surch__7897ECEC] DEFAULT (0),
[SurchargeAdjust_moving] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__Surch__798C1125] DEFAULT (0.0),
[FixedCharge_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__Fixed__7A80355E] DEFAULT (0),
[FixedCharge_moving] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__Fixed__7B745997] DEFAULT (0),
[OfferedPriceP1_Edit_fast] [float] NOT NULL,
[OfferedPriceP2_Edit_fast] [float] NOT NULL,
[OfferedPriceP3_Edit_fast] [float] NOT NULL,
[OfferedPriceP4_Edit_fast] [float] NOT NULL,
[OfferedSurcharge_Edit_moving] [float] NOT NULL,
[movingTak_moving] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__movingT__7C687DD0] DEFAULT (0),
[movingTakPremie_moving] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__movingT__7D5CA209] DEFAULT (0),
[C_VolumeDelivery] [float] NOT NULL,
[C_ChargePerUnit] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Cha__7E50C642] DEFAULT (0),
[C_SurchargeBase] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sur__7F44EA7B] DEFAULT (0),
[C_VolumeSurcharge_moving] [float] NOT NULL,
[C_VolumeCost] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__00390EB4] DEFAULT (0),
[C_VolumeCostP1] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__012D32ED] DEFAULT (0),
[C_VolumeCostP2] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__02215726] DEFAULT (0),
[C_VolumeCostP3] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__03157B5F] DEFAULT (0),
[C_VolumeCostP4] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__04099F98] DEFAULT (0),
[C_VolumeDeliveryP1] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__04FDC3D1] DEFAULT (0),
[C_VolumeDeliveryP2] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__05F1E80A] DEFAULT (0),
[C_VolumeDeliveryP3] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__06E60C43] DEFAULT (0),
[C_VolumeDeliveryP4] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__07CM307C] DEFAULT (0),
[C_SurchargUYNrmin_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sur__08CE54B5] DEFAULT (0),
[C_SurchargUYNrminP1_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sur__09C278EE] DEFAULT (0),
[C_SurchargUYNrminP2_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sur__0AB69D27] DEFAULT (0),
[C_SurchargUYNrminP3_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sur__0BAAC160] DEFAULT (0),
[C_SurchargUYNrminP4_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sur__0C9EE599] DEFAULT (0),
[C_movingSurcharge_moving] [float] NOT NULL,
[C_EffectSurcharge_moving] [float] NOT NULL,
[C_BalanceSurcharge_moving] [float] NOT NULL,
[C_ProfileSurcharge_moving] [float] NOT NULL,
[C_movingKundPrisTak_moving] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Spo__0D9309D2] DEFAULT (0),
[C_SeparateCost_fast] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sep__0E872E0B] DEFAULT (0),
[C_SeparateCost_moving] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Sep__0F7B5244] DEFAULT (0),
[C_VolumePrice] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__106F767D] DEFAULT (0),
[C_VolumePriceP1] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__11639AB6] DEFAULT (0),
[C_VolumePriceP2] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__1257BEEF] DEFAULT (0),
[C_VolumePriceP3] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__134BE328] DEFAULT (0),
[C_VolumePriceP4] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Vol__14400761] DEFAULT (0),
[C_MonthFirst] [smallint] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Mon__15342B9A] DEFAULT (0),
[C_MonthLast] [smallint] NOT NULL CONSTRAINT [DF__tblSaleOb__C_Mon__16284FD3] DEFAULT (0),
[C_SurchargeSaleAdjust_AP] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeSaleAdjust_AP] DEFAULT (0),
[C_HourlyPercentage] [float] NOT NULL CONSTRAINT [DF_MyTable_C_HourlyPercentage] DEFAULT (0),
[C_OffertRisk_fast] [float] NOT NULL CONSTRAINT [DF_MyTable_C_OffertRisk_fast] DEFAULT (0.0),
[C_SurchargeCertificate_fast] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeCertificate_fast] DEFAULT (0.0),
[C_TB_Sum] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_TB___23582DBF] DEFAULT (0),
[C_TB_PerUnit] [float] NOT NULL CONSTRAINT [DF__tblSaleOb__C_TB___244C51F8] DEFAULT (0),
[SurchargeMarketInvestment_fast] [float] NOT NULL DEFAULT ((0)),
[SurchargeMarketInvestment_moving] [float] NOT NULL DEFAULT ((0)),
[C_StructuralCostSurcharge] [float] NOT NULL DEFAULT ((0)),
[C_FASTPRISBAS_P0] [float] NOT NULL DEFAULT ((0)),
[C_movingPRISBAS_P0] [float] NOT NULL DEFAULT ((0)),
[C_StructuralCostSurcharge_fast] [float] NOT NULL DEFAULT ((0)),
[C_StructuralCostSurcharge_moving] [float] NOT NULL DEFAULT ((0)),
[C_StructuralCostCoefficient] [float] NOT NULL DEFAULT ((0.0)),
[C_Other_Swedish_Storage] [float] NOT NULL DEFAULT ((0.0)),
[C_Physical_Profile_Risk] [float] NOT NULL DEFAULT ((0.0)),
[C_Transport_Plus_Storage_Included] [float] NOT NULL DEFAULT ((0.0)),
[C_Transport_Plus_Storage_Separate] [float] NOT NULL DEFAULT ((0.0)),
[C_Transmission_TE_IUP_HYU] [float] NOT NULL DEFAULT ((0.0)),
[SellerSurcharge_IUP_HYU] [float] NOT NULL DEFAULT ((0.0)),
[AdjustedSurcharge_IUP_HYU] [float] NOT NULL DEFAULT ((0.0)),
[C_BuyOtherPe] [float] NOT NULL DEFAULT ((0.0)),
[BuySurcharge_HYU_in_EFR] [float] NOT NULL DEFAULT ((0.0)),
[BuySurcharge_EFR_in_HYU] [float] NOT NULL DEFAULT ((0.0)),
[Offered_BuySurcharge_HYU] [float] NOT NULL DEFAULT ((0.0)),
[C_Profile_Cost_CM_EFR] [float] NOT NULL DEFAULT ((0.0)),
[C_Volume_Risk] [float] NOT NULL DEFAULT ((0.0)),
[C_Other_LJR_BDU] [float] NULL,
[CorrectionValue] [float] NOT NULL DEFAULT ((0)),
[C_UYN_Exit_Cost] [float] NOT NULL DEFAULT ((0.0)),
[C_CapacityCost_HYU_Included] [float] NOT NULL CONSTRAINT [DF_MyTable_C_CapacityCost_HYU_Included] DEFAULT ((0)),
[C_CapacityCost_HYU_Separate] [float] NOT NULL CONSTRAINT [DF_MyTable_C_CapacityCost_HYU_Separate] DEFAULT ((0)),
[C_SurchargeP1_Etage_HYU] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP1_Etage_HYU] DEFAULT ((0)),
[C_SurchargeP2_Etage_HYU] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP2_Etage_HYU] DEFAULT ((0)),
[C_SurchargeP3_Etage_HYU] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP3_Etage_HYU] DEFAULT ((0)),
[C_SurchargeP4_Etage_HYU] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP4_Etage_HYU] DEFAULT ((0)),
[C_SurchargeP1_Etage_EFR] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP1_Etage_HYU1] DEFAULT ((0)),
[C_SurchargeP2_Etage_EFR] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP2_Etage_HYU1] DEFAULT ((0)),
[C_SurchargeP3_Etage_EFR] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP3_Etage_HYU1] DEFAULT ((0)),
[C_SurchargeP4_Etage_EFR] [float] NOT NULL CONSTRAINT [DF_MyTable_C_SurchargeP4_Etage_HYU1] DEFAULT ((0)),
[C_Surcharge_Etage_HYU] [float] NOT NULL CONSTRAINT [DF_MyTable_C_Surcharge_Etage_HYU] DEFAULT ((0)),
[C_Surcharge_Etage_EFR] [float] NOT NULL CONSTRAINT [DF_MyTable_C_Surcharge_Etage_EFR] DEFAULT ((0)),
CONSTRAINT [MyTable2_PK] PRIMARY KEY NONCLUSTERED
(
[DeliveryYearID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] WITH CHECK ADD CONSTRAINT [MyTable3_MyTable_FK1] FOREIGN KEY([DeliveryID])
REFERENCES [dbo].[MyTable3] ([DeliveryID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [MyTable3_MyTable_FK1]
Update 2:
Found the column causing the error: [C_SurchargUYNrminP2_fast]
select top 15 [C_SurchargUYNrminP2_fast] from MyTable
Update 3:
Tested the query suggested by @LukStorms. Error received:
Msg 9100, Level 23, State 2, Line 2 Possible index corruption detected. Run DBCC CHECKDB.
-- Change col1 to your actual primary key column(s), col2 to the column from the 2570 error, table1 to the table from the CHECKDB output
SELECT col1, col2 FROM table1
WHERE col2<>0.0 AND (col2 < 2.23E-308 OR col2 > 1.79E+308) AND (col2 < -1.79E+308 OR col2 > -2.23E-308)
Update 4:
Command: DBCC CHECKTABLE(N'dbo.MyTable') WITH DATA_PURITY
Msg 2570, Level 16, State 3, Line 1 Page (1:30707), slot 8 in object ID 1330364054, index ID 1, partition ID 72057615345844224, alloc unit ID 72057615349710848 (type "In-row data"). Column "C_SurchargUYNrminP2_fast" value is out of range for data type "float". Update column to a legal value.
Thanks to @DanGuzman and everyone else in comments.
Found the corrupt values with the command:
DBCC CHECKTABLE(N'dbo.MyTable') WITH DATA_PURITY
Msg 2570, Level 16, State 3, Line 1 Page (1:30707), slot 8 in object ID 1330364054, index ID 1, partition ID 72057615345844224, alloc unit ID 72057615349710848 (type "In-row data"). Column "C_SurchargUYNrminP2_fast" value is out of range for data type "float". Update column to a legal value.
I could then use DBCC PAGE to find the corrupt value and the corresponding primary key. With this help I could update the value.
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/10/how-to-use-dbcc-page/
DBCC TRACEON ( 3604 )
DBCC PAGE ( 'myDB' , 1 , 30707 , 3 )
DBCC TRACEOFF ( 3604 )
Slot 8 Column 1 Offset 0x4 Length 4
DeliveryID = 123
Slot 8 Column 33 Offset 0xf0 Length 8
C_SurchargUYNrminP2_fast = -1.#IND