Anyone see a query timeout even though the settings are set to not be limited in the various spots?
Kelly
what is the query you are running? Is this a new BAQ? mind posting the query phrase?
1 Like
It’s a baq that previously was working and grabs BOM information. I’m more confused why it’s still capping at 30 seconds even though its basically been told to be unlimited.
Kelly
Have you tried to set the server to something other than 0? lets say 600.
just to see if the timeout setting is working. it looks like the 30 seconds limit was still in effect.
I only ask about the display query is then there are other areas that can be looked at as well.
When it worked, how long did it take? It seems odd that you would need a query that would take that long.
1 Like
I know there is likely some issues with the query and will start to diagnose that. As mentioned, I was more surprised it wasn’t honoring the timeout values.
For what it’s worth, it seems the 0 timeout setting in the BAQ doesn’t seem to actually be unlimited. If I change the # to something else as Ken suggested (I chose 500) then it will hit that timer. On the flip side changing the server side limit to say 200, it didn’t stop at 200… I’m still confused on how the 2 limits work but obviously the BAQ needs to be re-worked.
Kelly
post the query more eyes the merrier… 
with [AnchorQuery] as
(select
[PartRev].[Company] as [PartRev_Company],
(0) as [Calculated_MtlSeq],
[PartRev].[PartNum] as [PartRev_PartNum],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
(0) as [Calculated_Level],
(cast (1 as decimal(7,4))) as [Calculated_QtyPer],
(cast (1 as decimal(7,4))) as [Calculated_ExtQty],
(cast(PartRev.PartNum as varchar(100))) as [Calculated_Sortit]
from Erp.PartRev as PartRev
inner join Erp.PartPlant as PartPlant on
PartRev.Company = PartPlant.Company
and PartRev.PartNum = PartPlant.PartNum
and PartRev.Plant = PartPlant.Plant
inner join Erp.Part as Part1 on
PartPlant.Company = Part1.Company
and PartPlant.PartNum = Part1.PartNum
and ( Part1.Method = 1 )
where (PartRev.Approved = true and PartRev.EffectiveDate <= @Today and PartRev.AltMethod = ‘’)
union all
select
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[MtlPartRev].[RevisionNum] as [MtlPartRev_RevisionNum],
(AnchorQuery.Calculated_Level + 1) as [Calculated_ChildLevel],
(cast (PartMtl.QtyPer as decimal(7,4))) as [Calculated_ChildQtyPer],
(cast (PartMtl.QtyPer * AnchorQuery.Calculated_ExtQty as decimal(7,4))) as [Calculated_ChildExtQty],
(cast (AnchorQuery.Calculated_SortIt + ’ |’ + right(10000 + PartMtl.MtlSeq,4) as varchar(100))) as [Calculated_ChildSort]
from AnchorQuery as AnchorQuery
inner join Erp.PartMtl as PartMtl on
AnchorQuery.PartRev_Company = PartMtl.Company
and AnchorQuery.PartRev_PartNum = PartMtl.PartNum
and AnchorQuery.PartRev_RevisionNum = PartMtl.RevisionNum
inner join Erp.PartRev as MtlPartRev on
PartMtl.Company = MtlPartRev.Company
and PartMtl.MtlPartNum = MtlPartRev.PartNum
and ( MtlPartRev.Approved = true and MtlPartRev.EffectiveDate <= @Today and MtlPartRev.AltMethod = ‘’ ))
select
[Anchor].[Calculated_Level] as [Calculated_Level],
[Anchor].[Calculated_MtlSeq] as [Calculated_MtlSeq],
[Anchor].[PartRev_PartNum] as [PartRev_PartNum],
[Anchor].[PartRev_RevisionNum] as [PartRev_RevisionNum],
(left(’. . . . . . .’,Anchor.Calculated_Level*3)+Part.PartDescription) as [Calculated_IndentedDescription],
[Anchor].[Calculated_QtyPer] as [Calculated_QtyPer],
[Anchor].[Calculated_ExtQty] as [Calculated_ExtQty],
[Anchor].[Calculated_Sortit] as [Calculated_Sortit],
[Part].[PartCreation_c] as [Part_PartCreation_c],
[Part].[TypeCode] as [Part_TypeCode],
[Part].[Saleable_c] as [Part_Saleable_c]
from AnchorQuery as Anchor
inner join Erp.Part as Part on
Company = Part.Company
and Anchor.PartRev_PartNum = Part.PartNum
order by Anchor.Calculated_Sortit
I would imagine that Epicor has some fail safes in place to make sure a customer doesn’t crash their own server inadvertently. If it was something that was actually an infinite loop, and not just something that took a while, it could cause problems, and emergency service calls, so I would guess that there are a couple (or more) limits set to prevent that.
I think that most of the time (like you stated), the root cause is problem with the query, and if that’s fixed, the timeout isn’t really the problem. Just the symptom.
so to answer this question:
Yes I have seen this before. I just fixed the query.
how many levels are the BOM’s?
Try running the BAQ in a SQL editor or in Toad. Wondering if there is an index that needs some TLC. The query looks like it should run without any timing issues.
Could it be a data problem? Circular reference maybe? Is MRP running OK?
Nice idea, I had just done this in SQL and saw a different error.
“Arithmetic overflow error converting numeric to data type numeric.”
Now I need to dig into which calculated field is likely causing that.
Kelly
bool WhatAreYouTalkingAbout()
{
return WhatAreYouTalkingAbout();
}
```
1 Like