btrieve
asked on
Job Executing Stored Procedure Fails on First Attempt, Succeeds on Second Attempt - Floating Point Exception
We have two steps in a job that runs overnight, each executes a stored procedure. The steps are 2 of 35 total steps. The job is set up to rerun the steps if they initially fail.
When the job attempts the steps the first time, they fail, giving us the following error "A floating point exception occurred in the user process. Current transaction is canceled. [SQLSTATE 42000] (Error 3628)." However, on the second attempt,
the steps succeed.
When the job attempts the steps the first time, they fail, giving us the following error "A floating point exception occurred in the user process. Current transaction is canceled. [SQLSTATE 42000] (Error 3628)." However, on the second attempt,
the steps succeed.
--**This code is the first step in question, step 9 of 35. It was implemented to counter #IND errors that were thought to
--**exist in the data.
update orditemhis
set cost=0, margin=0, annualizedmargin=0
where shipdate between '2006-01-01' and '2010-12-31'
and convert(char(20), cost) like '%ind%'
update orditem
set cost=0, margin=0, annualizedmargin=0
where shipdate between '2006-01-01' and '2010-12-31'
and convert(char(20), cost) like '%ind%'
--**This is the second step in question, step 17 of 35. Again, both steps succeed only on the second attempt by the job.
--Exec _sp_DW_UpdSalesIAFactPosted '2006-01-01', '2010-12-31'
--Here is the code for _sp_DW_UpdSalesIAFactPosted
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE DBO._sp_DW_UpdSalesIAFactPosted @FromDate as DateTime, @ToDate as DateTime
AS
---------------- update fact table with posted data
BEGIN TRAN
select A.AreaID, C.CustomerID, Pr.ProductID, T.TimeID, S.SalesRepID, D.DivisionID, Sp.ServiceProgramID,
Sk.SkuId, Ca.CarrierID, CR.CreditReasonID, tg.TimeId as GLTimeId,
PromoID = Case When O.PromoNum = 0 Then 1 Else 2 End,
QtySales = i.qty - i.qty*o.creditwo,
FobSales = i.qty*i.fobPrice - i.qty*i.fobPrice*o.creditwo,
PackagingSales = i.qty*i.PackagingCharge - i.qty*i.PackagingCharge*o.creditwo,
FreightSales = i.qty*i.FreightCharge - i.qty*i.FreightCharge*o.creditwo,
GuaranteeSales = i.qty*i.GuaranteeRevenue - i.qty*i.GuaranteeRevenue*o.creditwo,
ServiceSales = i.qty*i.ServiceRevenue - i.qty*i.ServiceRevenue*o.creditwo,
RebateSales = i.qty*i.RebateRevenue - i.qty*i.RebateRevenue*o.creditwo,
QtyCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then 0 Else i.qty*o.creditwo End, -- Admin Credit
FobCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then 0 Else i.qty*i.fobPrice*o.creditwo End,
PackagingCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then 0 Else i.qty*i.PackagingCharge*o.creditwo End,
FreightCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then 0 Else i.qty*i.FreightCharge*o.creditwo End,
GuaranteeCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then 0 Else i.qty*i.GuaranteeRevenue*o.creditwo End,
ServiceCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then 0 Else i.qty*i.ServiceRevenue*o.creditwo End,
RebateCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then 0 Else i.qty*i.RebateRevenue*o.creditwo End,
pr_QtyCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then i.qty*o.creditwo Else 0 End, -- Production Credit
pr_FobCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then i.qty*i.fobPrice*o.creditwo Else 0 End,
pr_PackagingCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then i.qty*i.PackagingCharge*o.creditwo Else 0 End,
pr_FreightCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then i.qty*i.FreightCharge*o.creditwo Else 0 End,
pr_GuaranteeCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then i.qty*i.GuaranteeRevenue*o.creditwo Else 0 End,
pr_ServiceCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then i.qty*i.ServiceRevenue*o.creditwo Else 0 End,
pr_RebateCredits = Case When I.ReasonCode in ('07','11','12','13','23','24','27','31','34','40','49','52','53','54','64') Then i.qty*i.RebateRevenue*o.creditwo Else 0 End,
i.OrderNum as WorkOrderNo,
isNull(i.Margin,0) as Margin,
isNull(i.AnnualizedMargin,0) as AnnualizedMargin,
i.qty*i.cost as FobCost,
i.qty*i.FreightCost as FreightCost,
i.qty*i.PackagingCost as PackagingCost,
i.qty*i.ServiceCost as ServiceCost,
i.qty*i.RebateCost as RebateCost,
CommissionSales =
case when (a.division<>'CNF' and isnull(s.company,0)=2 and p.PlantCode in ('POI01','POI02','POI03','POI04','POI05','POI06','POI08','POI45','POIBW','POIPR','PON08','PWR08','PWR06','MBP6 ','MBP8 ','PNTTC','BPO10','PWR6 ','POI2G','PWR65','POIWM','FPGCS','PWR45'))
--or (p.non_commisionabl=1)
then 0
else Case When O.PromoNum = 0
Then case when isnull(dg.rm_document_type_all,7)<>7 then i.qty*(i.fobPrice+isnull(i.GuaranteeRevenue,0)+i.FreightCharge) else 0 end --Lump debits together with SaleCommissions (i.e. PBS Debits reflect actual sales of items) Mostly for the sake of the commission date.
Else case when isnull(dg.rm_document_type_all,7)<>7 then i.qty*(i.fobPrice+isnull(i.GuaranteeRevenue,0)+i.FreightCharge + PackagingCharge) else 0 end
End
end
,
CommissionCredits =
case when (a.division<>'CNF' and isnull(s.company,0)=2 and p.PlantCode in ('POI01','POI02','POI03','POI04','POI05','POI06','POI08','POI45','POIBW','POIPR','PON08','PWR08','PWR06','MBP6 ','MBP8 ','PNTTC','BPO10','PWR6 ','POI2G','PWR65','POIWM','FPGCS','PWR45'))
--or (p.non_commisionabl=1)
then 0
else Case When O.PromoNum = 0
Then case when isnull(dg.rm_document_type_all,1)<>7 then 0 else i.qty*(i.fobPrice+isnull(i.GuaranteeRevenue,0)+i.FreightCharge) end
Else case when isnull(dg.rm_document_type_all,1)<>7 then 0 else i.qty*(i.fobPrice+isnull(i.GuaranteeRevenue,0)+i.FreightCharge + PackagingCharge) end
End
end
,
TakenById = TB.SalesRepId,
1 as PostedId -- = Posted
,i.Status
,TrxTypeId= (select TrxTypeId from vTrxType where ordernum=o.ordernum and status='POSTED')
,o.PromoNum
,isnull(o.TruckNum,0) as TruckNum
into #TempTable
from orderhis o with (nolock)
join orditemhis i with (nolock) on i.ordernum = o.ordernum and isnull(Parent,'')=''
Left Join Customer CU with (nolock) On CU.CustCode = O.CustCode
Left Join vStore St with (nolock) On St.CustCode = O.CustCode and St.StoreCode = O.StoreCode
Left Join plant p with (nolock) on i.upc = p.upc
Left Join DW_CNF.DBO.Area A with (nolock) ON A.AreaCode = I.AreaCode and
a.division = case when o.origin = 'CR' then 'CR'
when o.origin = 'PA' then 'PA'
when i.areaCode < '800' or i.areacode > '899' then 'CNF'
else 'CC'
end
Left Join DW_CNF.DBO.Customer C with (nolock) ON isnull(C.CustType,'') = isnull(CU.CustType,'') and
isnull(C.ARCode,'') = isnull(CU.ARCode,'') and
isnull(C.CustCode,'') = isnull(O.CustCode,'') and
isnull(C.Region,'') = isnull(St.RegionCode,'') and
isnull(C.District,'') = isnull(St.ModifiedDistrict,'')
and C.StoreCode = St.ModifiedStoreCode
left Join DW_CNF.DBO.Product Pr with (nolock) ON pr.PlantCode = p.plantcode and
pr.family = p.family and
isnull(pr.segment,'') = isnull(p.segment,'') and
isnull(pr.category,'') = isnull(p.category,'') and
isnull(pr.potsize,'') = isnull(p.potsize,'') and
isnull(pr.description,'') = isnull(p.description,'')
Left Join DW_CNF.DBO.Time T with (nolock) On T.The_Date = O.ShipDate
Left Join DW_CNF.DBO.SalesRep S with (nolock) ON S.SalesRepCode = O.SalesRepCode
Left Join DW_CNF.DBO.SalesRep TB with (nolock) ON TB.SalesRepCode = O.TakenBy
Left Join DW_CNF.DBO.Division D with (nolock) ON D.DivisionCode = (case o.Origin when 'MCF' then 'CNF' else o.Origin end)
Left Join DW_CNF.DBO.ServiceProgram Sp with (nolock) ON O.IsServiceProgram = Sp.IsServiceProgramBin
Left Join DW_CNF.DBO.SKU Sk with (nolock) ON Sk.SKUNumber = Case When I.AlternateSku <> '' Then I.AlternateSKU Else IsNull(I.SKU,'') end
Left Join DW_CNF.DBO.Carrier Ca with (nolock) ON Ca.CarrierCode = IsNull(O.CarrierCode,'') and Ca.TruckNumber = IsNull(O.TruckNum,0)
Left Join DW_CNF.DBO.CreditReason CR with (nolock) ON CR.ReasonCode = IsNull(I.ReasonCode,'')
Left Join cnf.dbo.vDynRMTransactions dg on dg.document_number = o.invoiceno and dg.rm_document_type_all = case o.creditwo when 0 then '1' else case when o.amount>0 then '3' else '7' end end
join dw_cnf.dbo.time tg with (nolock) on tg.the_date=case isnull(dg.gl_posting_date,0) when 0 then '1900-01-01' else dg.gl_posting_date end
Left Join DW_CNF.Dbo.FactTable F with (nolock) ON F.OrderNum = I.OrderNum and F.FactTableName = 'SALESIAFACT'
where o.ShipDate >= @FromDate and o.ShipDate <= @ToDate
and o.status = 8 and o.Amount <> 0
and F.FactTableID is Null
INSERT DW_CNF.DBO.SalesIAFact (AreaID, CustomerID, ProductID, TimeID, SalesRepID, DivisionID, ServiceProgramID, SkuId, CarrierID, CreditReasonID, GLTimeId,
PromoID, QtySales, FobSales, PackagingSales, FreightSales, GuaranteeSales, ServiceSales, RebateSales, QtyCredits,
FOBCRedits, PackagingCredits, FreightCredits, GuaranteeCredits, ServiceCredits, RebateCredits, pr_QtyCredits,
pr_FOBCRedits, pr_PackagingCredits, pr_FreightCredits, pr_GuaranteeCredits, pr_ServiceCredits, pr_RebateCredits,
WorkOrderNo, Margin, AnnualizedMargin, FOBCost, FreightCost, PackagingCost, ServiceCost, RebateCost, CommissionSales, CommissionCredits,TakenById, PostedID,TrxTypeId, PromoNum, TruckNum)
select
AreaID, CustomerID, ProductID, TimeID, SalesRepID, DivisionID, ServiceProgramID, SkuId, CarrierID, CreditReasonID, GLTimeId,
PromoID, QtySales
, cast(FobSales as decimal(10,2))
, cast(PackagingSales as decimal(10,2))
, cast(FreightSales as decimal(10,2))
, cast(GuaranteeSales as decimal(10,2))
, cast(ServiceSales as decimal(10,2))
, cast(RebateSales as decimal(10,2))
, QtyCredits
, cast(FOBCRedits as decimal(10,2))
, cast(PackagingCredits as decimal(10,2))
, cast(FreightCredits as decimal(10,2))
, cast(GuaranteeCredits as decimal(10,2))
, cast(ServiceCredits as decimal(10,2))
, cast(RebateCredits as decimal(10,2))
, cast(pr_QtyCredits as decimal(10,2))
, cast(pr_FOBCRedits as decimal(10,2))
, cast(pr_PackagingCredits as decimal(10,2))
, cast(pr_FreightCredits as decimal(10,2))
, cast(pr_GuaranteeCredits as decimal(10,2))
, cast(pr_ServiceCredits as decimal(10,2))
, cast(pr_RebateCredits as decimal(10,2))
, WorkOrderNo
, cast(Margin as decimal(10,2))
, cast(AnnualizedMargin as decimal(10,2))
, cast(FOBCost as decimal(10,2))
, cast(FreightCost as decimal(10,2))
, cast(PackagingCost as decimal(10,2))
, cast(ServiceCost as decimal(10,2))
, cast(RebateCost as decimal(10,2))
, cast(CommissionSales as decimal(10,2))
, cast(CommissionCredits as decimal(10,2))
,TakenById, PostedID,TrxTypeId, PromoNum, TruckNum
from #TempTable t
where t.status<>9
and productid is not null
and customerid is not null
------------ Update Source Data Flag (DW_CNF_SalesIA) ------------
IF @@ERROR <> 0
BEGIN
ROLLBACK
Print 'Error Inserting Records to DW'
END
ELSE
BEGIN
COMMIT
Print 'Inserting Records to DW where commited'
Insert DW_CNF.Dbo.FactTable (OrderNum, FactTableName)
Select Distinct O.OrderNum, 'SALESIAFACT'
From OrderHis O with (nolock)
Left Join DW_CNF.Dbo.FactTable F with (nolock) ON F.OrderNum = O.OrderNum and F.FactTableName = 'SALESIAFACT'
where o.ShipDate >= @FromDate and o.ShipDate <= @ToDate and o.status = 8 and F.FactTableID is Null
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.