Solved

Job Executing Stored Procedure Fails on First Attempt, Succeeds on Second Attempt - Floating Point Exception

Posted on 2008-10-29
2
409 Views
Last Modified: 2012-05-05
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.



--**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

Open in new window

0
Comment
Question by:btrieve
2 Comments
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 100 total points
Comment Utility
OK, something to consider. Something is happening during the first 8 steps that causes step 9 to fail. Something is happening in steps 1-16 that causes step 17 to fail. Whatever is happening is likely related to data, commits or indexes. Pretty vague huh. But my guess is the problem gets resolved in the end of the process. The next time it runs the events that cause the issue do not occur because data has already been processed and nothing occurs. Anyway, just a guess to pursue.
0
 

Accepted Solution

by:
btrieve earned 0 total points
Comment Utility
The funny thing is that if you run step 17 twice, the problem is resolved. What would cause a stored procedure to succeed only after being run twice?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now