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

We having some performance issues today (a lot of blocking process alerts) and while investigating ran into this

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 

For some reason “some process” within Epicor is re-creating the listEntry function… which exists and has existed for years within Epicor. This isn’t the first time I catch this and it makes zero sense.

Why does Epicor re-create a SQL function that already exists? While a ton of its own programs and applications use it which leads to blocks and mayhem. This doesn’t make any sense.

@SAD , @Olga @Edge @Patrick.Ferrington

Any ideas why this happens?

1 Like

What kind of sp’s are you using to get these results?

4 Likes

Right on, I thought it looked like that, wanted to confirm in case there was a different tool you were using that you liked more.

I also love this one
http://whoisactive.com/

1 Like

Not sure why you are seeing a create function for that - is it in the context of a BAQ execution? If so get a support called raised and the team can review.

Yes it appears to be during a BAQ Execution I’ve seen it many many times (though not always). I can open a support ticket but man that’s going to be painful! lol

2 Likes

Is this related to what you had before:

Also I get similar ones but diff:

Yup same thing. (Still)

1 Like

@josecgomez do you guys have SQL Enterprise? If so by default BAQs will run against a Readonly database. I wonder if you can check if all your Databases in Cluster have the function, or if you can in the BAQ Properties see if the error goes away when you use “Use Primary Database”.
image

Second maybe the Service Account you configured in Admin Console to use doesn’t have permissions to list Functions and so it makes an assumption it doesnt exist.

I know Epicor defines ListEntry in their .edmx maybe Entity Framework is the one trying to create it again.

1 Like

Function are there , no SQL Enterprise, and the account running is Epicor DB owner. I do believe is coming out of the EDMX but it doens’t make snese why

You are right, the function comes from EDMX/ErpContext. But I have no clue why EF tries to recreate it. And I doubt that it is a BAQ-related issue.

2 Likes

I haven’t seen it in my logs. I even ran various BAQs yesterday with Calculated Fields using Ice… Updateable BAQs… Havent been able to replicate it.

1 Like

It causes blocking issues when / if this runs during someone else executing a query that uses it… it’s so crazy when it happens

1 Like

I would bet that we see it more than other customers because we rely VERY heavily on that stored procedures based on the weird business rules our commission program forces us into. I would bet that it happens with other customers, but it’s rare enough, where no one is seeing the impact.

1 Like

Yeah also we have SQL monitoring which alerts us actively when it happens

It is still frustrating and it makes no sense

1 Like

The commission sales rep list is in a string that has to be broken out into a list, is that what is causing it?

Yeah, the sales agent list, which is ~ delimited. There is a handy stored procedure to help you split that string, because SQL doesn’t have anything pre-built to do that for you. However, it’s causing problems.

1 Like

Interesting…

Stephen is right. You need to bother the support.