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.
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
@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”.
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.
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
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.
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.
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.
