Error : Arithmetic overflow error converting numer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error : Arithmetic overflow error converting numer

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2012-07-15 : 23:16:34

IF OBJECT_ID('Customer.dbo.Customers) IS NOT NULL
DROP TABLE Customer.dbo.Customers

CREATE TABLE Customer.dbo.Customers
(
CustomerID INT
, Date DATETIME
, Active INT
, CustomerName VARCHAR (100)

)

DECLARE @StartNumber INT
DECLARE @StartMinus1 INT
DECLARE @EndNumber INT

SET @StartNumber =10
SET @StartMinus1 =9
SET @EndNumber =1

WHILE (@StartMinus1 > 1)
BEGIN

INSERT INTO Customer.dbo.Customers

SELECT *
FROM Customers

WHERE (Date BETWEEN GETDATE() - @StartNumber AND GETDATE () - @StartMinus1 )

PRINT @StartNumber
PRINT @StartMinus1

END

It did print off everything correctly but I have not figured out why I'm getting the error

Msg 8115, Level 16, State 8, Line 56
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
9
8
Msg 8115, Level 16, State 8, Line 56
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
8
7

ETC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-15 : 23:35:30

is there some insert trigger on Customer.dbo.Customers?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2012-07-16 : 00:33:08

visakh16,

There should have been. I was so into thinking about the loop I forgot the most basic thing on my insert statement LOL.... Thank you

For anyone interested, the reason I was doing something in this fashion is if I pulled a day at a time (15 + million records) it was pulling way too slow for large chunks, but when I did a day at a time it was very quick.

IF OBJECT_ID('Customer.dbo.Customers) IS NOT NULL
DROP TABLE Customer.dbo.Customers

CREATE TABLE Customer.dbo.Customers
(
CustomerID INT
, Date DATETIME
, Active INT
, CustomerName VARCHAR (100)

)

DECLARE @StartNumber INT
DECLARE @StartMinus1 INT
DECLARE @EndNumber INT

SET @StartNumber =10
SET @StartMinus1 =9
SET @EndNumber =1

WHILE (@StartMinus1 > 1)
BEGIN

INSERT INTO Customer.dbo.Customers
(CustomerID , Date , Active, CustomerName )

SELECT CustomerID , Date , Active, CustomerName
FROM Customers

WHERE (Date BETWEEN GETDATE() - @StartNumber AND GETDATE () - @StartMinus1 )

PRINT @StartNumber
PRINT @StartMinus1

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-16 : 05:59:53

I think your WHILE will never end becuase you have not decremented the value of @StartMinus1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 10:04:42

yep...Thats true...Nice catch Madhi

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources