I'm troubleshooting a stored procedure that suddenly decided to stop working. I narrowed down the problem to the last part of the stored procedure where it selects data from a temp table and inserts it into a physical table in the SQL2000 database.
I keep receiving the following error:
Server: Msg 8115, Level 16, State 8, Line 140
Arithmetic overflow error converting numeric to data type numeric.
The data values all appear to be correct with none of them seeming to be out of precision, but I keep getting the error. I've tried casting all the values and it didn't work. It executes w/o error when I comment out that particular insert. I just don't get it.
Any help would be appreciated. Thanks.
Code below:
-------------------------------------------------------------
declare @dtAsOfdate DATETIME
set @dtAsOfDate = '2006-04-16';
DECLARE @RC INTEGER
-------------------------
-- 1) Eligible Investments:
-------------------------
-- Input: @SPVId - SPV we are running process for
-- @Yes - value of enum CCPEnum::eYesNoYes (get by lookup).
-- Output: Recordset (temp table) of Collaterals that are eligible for MV Test (#MVTriggerInvestments).
DECLARE @Yes INTEGER
EXEC @RC = [dbo].CPLookupVal 'YesNo', 'Yes', @Yes OUTPUT
IF (@RC<>0)BEGIN
RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes enum', 16, 1) WITH SETERROR
END
drop table #MVTriggerInvestments
BEGIN
SELECT dbal.SPVId,
dbal.CusipId,
dbal.GroupId,
@dtAsOfDate AS AsOfDate,
dbal.NormalOCRate,
dbal.SteppedUpOCRate,
dbal.AllocMarketValue AS MarketValue,
dbal.NbrDays,
dbal.PriceChangeRatio
INTO #MVTriggerInvestments
FROM DailyCollateralBalance dbal
JOIN CollateralGroupIncludeInOC gin
ON dbal.SPVId = 2
AND gin.SPVId = 2
AND dbal.AsOfDate = '2006-04-16'
AND @dtAsOfDate BETWEEN gin.EffectiveFrom AND gin.EffectiveTo
AND dbal.GroupId = gin.GroupId
AND gin.IncludeInOC = @Yes
END
select * from #MVTriggerInvestments
print 'end #1'
--select * from #MVTriggerInvestments --looks ok
--------------------------------------------------------------
-- 2) Calculate Weighted Average Price change ratio Market Value (by Group):
-- PCRMV - Price Change Ratio Market Value
--------------------------------------------------------------
-- Input : Recordset of collaterals (having New/Old prices, MarketValue defined)
-- Output: Recordset Aggregated by Group (#GroupOCRate)
drop table #MVTriggerGroup
BEGIN
SELECT A.SPVId,
A.AsOfDate,
A.GroupId,
A.NormalOCRate,
A.SteppedUpOCRate,
A.MarketValue,
cast([dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as numeric(12,9)) as PriceChangeRatio,
CAST (0 AS NUMERIC(12,9)) AS OCRate,
CAST ('' AS VARCHAR(6)) AS OCRateType,
CAST (0 AS NUMERIC(18,2)) AS DiscMarketValue,
CAST (0 AS NUMERIC(18,2)) AS InterestAccrued
INTO #MVTriggerGroup
FROM
(
SELECT SPVId,
AsOfDate,
GroupId,
NormalOCRate,
SteppedUpOCRate,
cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue
FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate
) A --works up to here
JOIN
(SELECT SPVId,
cast(SUM(AllocMarketValue) as numeric(18,2)) AS MarketValueForPeriod ,
cast(SUM(AllocMarketValue * PriceChangeRatio) as numeric(18,2)) as PriceChangeRatioMarketValue,
GroupId
FROM T_DailyCollateralBalance
WHERE SPVId = 2
AND AsOfDate between '2006-03-17' and '2006-04-15'
AND IsBusinessDay = 1
GROUP BY SPVId, GroupId
) B
ON A.SPVId = B.SPVId
AND A.GroupId = B.GroupId
END
print 'end #2'
---------------------------------------------
-- Calculate OCRate to apply for each group.
---------------------------------------------
BEGIN
UPDATE #MVTriggerGroup
SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
ELSE NormalOCRate
END),
OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
ELSE 'normal'
END)
END
print 'end #3'
-------------------------------------
-- Calculate discounted Market Value
-------------------------------------
UPDATE #MVTriggerGroup
SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
print 'end #4'
---------------------------------
-- Insert data from temp tables
---------------------------------
-- 1)
select * from #MVTriggerInvestments
print 'begin tran'
BEGIN TRAN
DELETE T_MVTriggerInvestments
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
print 'DELETE T_MVTriggerInvestments'
--error is here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT T_MVTriggerInvestments
(
SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
OldPrice ,
NewPrice ,
PriceChangeRatio
)
SELECT SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
0.00 ,
0.00 ,
PriceChangeRatio
FROM #MVTriggerInvestments
print 'end mvtriggerinv select'
COMMIT TRAN
--end error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 2)
print 'begin tran 2'
BEGIN TRAN
DELETE T_OCTestGroup
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
INSERT T_OCTestGroup
(
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
SectionA ,
CPFace ,
IntExpense ,
Fees ,
SectionB ,
Receivables ,
IntReceivables ,
CashBalance ,
Investments ,
SectionC ,
ExcessCollateral,
MaxCPAllowed
)
SELECT
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0
FROM #MVTriggerGroup
print 'end tran 2'
COMMIT TRAN
Overflow error that doesn't make sense....
Hot Topic
- Can I modify my input data set in using DMX in mining structure?
- DTS Legacy and SQL 2005
- Problems installing SQL Server 2005 SP 1
- Alter column question
- auto expanding Temp DB to fit large requests
- Setup lanscape orientation in RS
- sproc call udf to generate alternating background color tags
- Basing Report on a Procedure with Cursor
- Frustrated with Service Broker Example
- Grouping question
SQL Server
- loading image dynamically
- SQL2005 SP1 install messages
- SQL2000 SP4 and simple DB logs not auto checkpointing
- Management Studio Scripting Bug?
- Win200K and MS SQL data retreival ?
- SP datetime Parameter problems
- SSIS will not start after SQL Server 2005 SP1 upgrade
- OLAP Data Distribution
- Where is variable used?
- GUID
Overflow error that doesn't make sense....
Steve.Adams
Mcrwire
Foxpro