Why is Epicor insisting in creating a function that already exists (SQL)

Kinetic ERP
21

At least now you can send them a link to this and ask them to let you know if they have any questions.

22

If others can’t replicate it, but you can replicate it, I imagine sending them your DB might be able to let them get the same issue?

23

You need to isolate when the problem is happening. Something specific about your version or database, or EDMX…

1 Like
24

I could see a Database Regen trying to create it or maybe on App Pool Start… but other then that :slight_smile: Would be interesting if you can find the BAQ and others can try it.

25

i cannot imagine why would any BAQ do it. So do not limit on BAQ.
I could see that EDMX checks DB for that function, and for some reason thinks it is different then it needs to be and recreates it. But what can cause it, no idea.

2 Likes
26

Jose does use Multiple App Servers and if you Regen on 1 and you dont recycle all of them your edmx may be different. Maybe one App Server edmx is out of sync and it treats it as “I should re-initialize”.

I always have to Regen Database and then Recycle all my App Servers for the .dll to update.

1 Like
27

We recycle all of them when we regen. Part of our standard process.

1 Like
28

After further looking at it we realized is erp.listEntry which isn’t called from a BAQ (baq uses Ice.Entry) so @Olga is right this isn’t necessarily a BAQ issue.
It appears that for some reason like she said the EDMX determines it needs to create (or re-create) that function… But why? :thinking:

1 Like
29

In my databases function text is different from what you show - in lower case:
SELECT OBJECT_DEFINITION (OBJECT_ID(‘Erp.listEntry’)) AS ObjectDefinition;

CREATE function [Erp].[listEntry](@idx int, @list nvarchar(max), @delimiter nchar(1))
returns nvarchar(1000)
as
begin
  declare @begin int, @end int, @count int
  declare @str nvarchar(max)
  set @count = 0
  set @begin = 1
  set @end = charindex(@delimiter, @list)
  if @list is null
	return (NULL)
  if @idx < 0
    return (NULL)
  if (@end = 0) and @idx > 0
    return (NULL)
  if (@end = 0) and @idx = 0
    return @list
  set @str = @list
  while charindex(@delimiter, @str) > 0 and @count < @idx
  begin
    set @begin = charindex(@delimiter, @str) + 1
    set @str = substring(@str, @begin, len(@str) - @begin + 1)
    set @count = @count + 1
  end
  if @count < @idx
    return (NULL)
  if charindex(@delimiter, @str) > 0
    set @str = substring(@str, 1, charindex(@delimiter, @str) - 1)
  else
    set @str = substring(@str, 1, len(@str))
  return (@str)
end
30

also, EDMX contains 3 function references, why do you see only one is recreated…

<edmx:StorageModels>
    <Schema xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
      <Function Name="ListCount" ReturnType="int" Schema="Erp" StoreFunctionName="listCount">
        <Parameter Name="list" Mode="In" Type="nvarchar" />
        <Parameter Name="delimiter" Mode="In" Type="nchar" />
      </Function>
      <Function Name="ListEntry" ReturnType="nvarchar" Schema="Erp" StoreFunctionName="listEntry">
        <Parameter Name="index" Mode="In" Type="int" />
        <Parameter Name="list" Mode="In" Type="nvarchar" />
        <Parameter Name="delimiter" Mode="In" Type="nchar" />
      </Function>
      <Function Name="ListLookup" ReturnType="int" Schema="Erp" StoreFunctionName="listLookup">
        <Parameter Name="item" Mode="In" Type="nvarchar" />
        <Parameter Name="list" Mode="In" Type="nvarchar" />
        <Parameter Name="delimiter" Mode="In" Type="nchar" />
      </Function>
    </Schema>
  </edmx:StorageModels>
31

I just grep searched the entire decompiled code base and the only place I see this function ever being used (server side) is in the NatAcctMassUpdProc…
Granted Reverse Engineered code isn’t always 100% accurate but with function/method names its generally pretty reliable.

@Olga looks like the function is the same, the code I copied from the active SQL execution monitor so casing and spacing could be way off. Or the SQL compiler may be modifying some of it… That is weird though

1 Like
32

That is a good question, not a clue… that seems to be the only one that “hangs” and creates a blocking process. (When it happens)

33

sp_help ‘Erp.listEntry’ shows when the function is created. maybe it coincide with app pool restart or some other activity?

1 Like
34

It shows it was “created” over 2 years ago, so that script that is running and trying to create the function i’m guessing failing or something because the created date isn’t being updated on the function.

1 Like
35

this is the list of all DB objects that use it. I only have 2 : listLookup and listEntry. Check what you have

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
WHERE m.definition Like '%listEntry%';
1 Like
36

Same as you just those 2.

1 Like