Help with Arithmetic Overflow error

Hi. I could use some help troubleshooting an error in a stored
procedure. The SP was developed some time ago and recently started
thowing this error:
Arithmetic overflow error converting numeric to data type numeric.
This error doesn't seem to make much sense since it happens when
inserting data into a physical table from a temp table in the stored
procedure. I've been pulling my hair out trying to figure out a way to
fix it. I'm pasting the code below along with my print statement and
comment showing where the error occurs. Any help is GREATLY
appreciated. Thanks!
--ALTER PROCEDURE [dbo].SP_OCCalculationMVTriggerTest
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,
[dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue,
B.MarketValueForPeriod, 0.00) 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,
SUM(MarketValue) AS MarketValue
FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate,
SteppedUpOCRate
) A --works up to here
JOIN
(SELECT SPVId,
SUM(AllocMarketValue) AS MarketValueForPeriod,
SUM(AllocMarketValue * PriceChangeRatio) 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 TRANThis is what the data looks like:
2 3128HDZR3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .15469
2 3128JLT66 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 22764923.64 693 .40260
2 3128JLVQ9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 33325930.88 693 .12272
2 3128JMY50 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 480 -.37161
2 3128NCB86 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 3128NCNV2 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 19 .00000
2 31295KSK1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 -.10075
2 31295NBU1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .97381
2 31295NCW6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .23484
2 31295NDA3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3445254.54 956 .51738
2 31295NMK1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .18800
2 31295NNJ3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4723304.57 956 -.06034
2 31295NUE6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 10974246.83 956 -.33549
2 31295NUG1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .09947
2 31295NUR7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3840958.96 956 -.41991
2 31295NZ76 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4679164.24 956 -.24881
2 31390YZH4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3333555.16 956 -.11334
2 31402C6D9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 46830311.23 689 .74464
2 31402DB25 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 689 .27197
2 31402DBZ2 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 689 .78266
2 31404EXL5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7785530.05 721 -.22302
2 31404LPV6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7814559.22 755 .72999
2 31404MBN7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 721 .03317
2 31405JJ21 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 480 -.26118
2 31409ARA9 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409BB71 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409BGD3 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 31409DSH7 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409DSM6 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 7 3083.50000
2 31409DVN0 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 31409UYN9 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 36225CWV4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 1575644.70 956 -.14477
2 36225DAY0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 8995207.86 693 -.17400
2 31337NND6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .00290
2 31349SEA7 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 26777158.30 875 -.32595
2 31349SHH9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 19822156.78 840 -.22582
2 31387A3M5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .50317
2 31391BKL0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4267353.02 956 -.98672
2 31391CJA4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7446628.34 956 -1.00060
2 31391NM65 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 2783766.24 956 -.33597
2 31404E2E5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 23112826.15 689 -.16876
2 31404EEJ1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 12078241.02 784 .10121
2 31404JRP2 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 16699637.14 755 .28834
2 31404JVR3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 10781379.91 755 .43313
2 31404JWZ4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 11040810.52 662 .46652
2 31404JXW0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3883162.72 721 .27610
2 31404JXZ3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .06 662 .00905
2 31404KRS3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 693 -.20539
2 36225C4Y9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 784 -.04323
2 36225C6Y7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 721 -.13550
2 36225C7B6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 19935755.89 721 -.15476
2 36225DAV6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 11457579.74 693 -.15476
Wish I could upload a spreadsheet...sql

Help w/ Data Structure

In a simple sales contact management software that I developed, users "own"
companies that are part of their portfolio. This is denoted by a row in the
UserCompany table. I use a join table because multiple users can own the
same company.
CREATE TABLE UserCompany
(
UserId int,
CompanyId int,
TargetCompany bit
)
The TargetCompany column is used to denote whether or not a certain company
is an important company in the specified user's portfolio. We run many
reports which only include a user's target companies.
I realized though, that if I run one of these reports for a date range that
is in the past, the data will always be based on the target companies
currently in UserCompany. i.e. Because of the data structure, I have no way
of telling if a user's company used to be a target company sometime in the
past.
Can someone suggest how I could modify this data structure to be able to
historically track when companies where denoted as target companies for a
certain user?
So, if Company A was denoted as a target company for User 1, between
01/01/2004, and 12/31/2004, but not later, how would I show that?
Thank YouHow about adding columns for StartOfInterestDate and EndOfInterest date,
denoting when the user had an interest in a particular company. You should
then be able to modify your query to include this within your criteria.
Cheers,
James Goodman
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
> In a simple sales contact management software that I developed, users
> "own" companies that are part of their portfolio. This is denoted by a row
> in the UserCompany table. I use a join table because multiple users can
> own the same company.
> CREATE TABLE UserCompany
> (
> UserId int,
> CompanyId int,
> TargetCompany bit
> )
> The TargetCompany column is used to denote whether or not a certain
> company is an important company in the specified user's portfolio. We run
> many reports which only include a user's target companies.
> I realized though, that if I run one of these reports for a date range
> that is in the past, the data will always be based on the target companies
> currently in UserCompany. i.e. Because of the data structure, I have no
> way of telling if a user's company used to be a target company sometime in
> the past.
> Can someone suggest how I could modify this data structure to be able to
> historically track when companies where denoted as target companies for a
> certain user?
> So, if Company A was denoted as a target company for User 1, between
> 01/01/2004, and 12/31/2004, but not later, how would I show that?
> Thank You
>|||Nice idea.
Let's say a company is a target company for 1 yr, then not a target company
for some time, and back to being a target company (phew)
Do you think this would be handled best with multiple rows for the same
User/Company?
"James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
> How about adding columns for StartOfInterestDate and EndOfInterest date,
> denoting when the user had an interest in a particular company. You should
> then be able to modify your query to include this within your criteria.
> --
> Cheers,
> James Goodman
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||Yes, sounds like you need a separate log table.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
> Nice idea.
> Let's say a company is a target company for 1 yr, then not a target
> company for some time, and back to being a target company (phew)
> Do you think this would be handled best with multiple rows for the same
> User/Company?
> "James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
> news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
>|||Thank you both for your help.
George
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:enaeB$kUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Yes, sounds like you need a separate log table.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
>|||CREATE TABLE UserCompanyHistory
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE,
company_id INTEGER NOT NULL
REFERENCES Companies(company_id)
ON UPDATE CASCADE,
company_priority INTEGER NOT NULL
CHECK (company_priority > 0),
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
end_date DATETIME, -- null means current\
CHECK(start_date, end_date),
PRIMARY KEY (user_id, company_id, start_date)) ;
Do not use the BIT datatype; use a prioirty number instead. Time is in
durations, so add start and end times to the table. Now for some
views:
CREATE VIEW UserCompany (user_id, company_id, company_priority)
AS SELECT user_id, company_id, user_id, company_priority
FROM UserCompanyHistory
WHERE end_date IS NULL;
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1
WHERE company_priority = 1;
or if you do not maintain a nice ordering in the priority column:
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1 WHERE company_priority
= (SELECT MIN(company_priority)
FROM UserCompanyHistory AS H2
WHERE H1.user_id = H2.user_id _
You will need some triggers to maintain the history table integrity,
but they are not tricky

HELP URGENT - Error 17832 after SQL 7 SP4 install and MS03-039 install

I have an application that was developed in an older version of VB (I think
4.0). The users are heavily dependent on this application (and of course,
no source code). Yesterday the users were able to connect to the SQL 7
database. Last night I ran the Service Pack 4 on the SQL 7 machine and
today the users are unable to connect. They just get an error message
(Invalid SQL Server Login). When I look in the error log on the server I
see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
login packet(s) sent. Connection closed.
Also, the latest security patch from Microsoft (MS03-039) was installed on
the server that houses the database. It is running Windows 2000.
Thanks
Any assistance would be greatly appreciated.Connie,
The only reference to 17832 that I could find was in this article:
INF: SQL Communication Errors 17832, 17824, 1608, 232, and 109 (KB Article
109787)
http://tinyurl.com/nfm8
17832 Unable to read login packet(s). [NT only]
This can happen if a client starts to connect, but never successfully
completes the attempt because of a client operating system or application
failure. It could also be caused by the network failing between the time a
connection attempt is initiated, and when it completes.
Of course, several changes were made, all at once, which is always risky.
(We always apply upgrades to a DEV or QA server before moving on to
production, if at all possible.)
This may be due to either SP4 or MS03-039. I see that MS03-039 patches RPC.
Although your application should (ideally) not need that facility I have no
way of knowing.
A more likely possibility: Did you us SP4 to update client software as
well? Some SPs have client and server components. Usually this is not a
problem, but sometimes it has caused grief.
Russell Fields
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23oGDh66eDHA.2352@.TK2MSFTNGP09.phx.gbl...
> I have an application that was developed in an older version of VB (I
think
> 4.0). The users are heavily dependent on this application (and of course,
> no source code). Yesterday the users were able to connect to the SQL 7
> database. Last night I ran the Service Pack 4 on the SQL 7 machine and
> today the users are unable to connect. They just get an error message
> (Invalid SQL Server Login). When I look in the error log on the server I
> see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
> login packet(s) sent. Connection closed.
> Also, the latest security patch from Microsoft (MS03-039) was installed on
> the server that houses the database. It is running Windows 2000.
> Thanks
> Any assistance would be greatly appreciated.
>|||You might want to check if the authentication mode got changed. Some of the
SQL service packs try to force Windows authentication or give the sa a
password other than blank. Go to SQL Server properties in SQL enterprise
manager, and check the security tab. See if you are in mixed mode or not.
From a DOS prompt on the server, you might also want to try
osql -U<user> -P<password> -S<servername>
for a user who cannot log on. That might give you a better error message.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23oGDh66eDHA.2352@.TK2MSFTNGP09.phx.gbl...
> I have an application that was developed in an older version of VB (I
think
> 4.0). The users are heavily dependent on this application (and of course,
> no source code). Yesterday the users were able to connect to the SQL 7
> database. Last night I ran the Service Pack 4 on the SQL 7 machine and
> today the users are unable to connect. They just get an error message
> (Invalid SQL Server Login). When I look in the error log on the server I
> see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
> login packet(s) sent. Connection closed.
> Also, the latest security patch from Microsoft (MS03-039) was installed on
> the server that houses the database. It is running Windows 2000.
> Thanks
> Any assistance would be greatly appreciated.
>|||how is this application connecting to the SQL Server, an ODBC like? is that
pointed ata Named Pipe connection, if it is try switching it to a TCP/IP
like,
if not Look at your SQL Client Network Utility, if the default is Named
Pipes or you have a Named Pipes Alias try switching it to TCP/IP.
MS0-039 is has a lot of cross over with MS03-026, both applied a lot of
security to connectivity through Named Pipes.
HtH