Virtual Happy Hour this month, Jun 28, we'll be getting a sneak preview at our soon to launch SaaS CMS!

Try our conversational search powered by Generative AI!

Bug in ecf_CatalogEntrySearch_Init when @DatabaseClockOffsetMS=0?


Hi Episerver

We are currently using the Episerver.Commerce.Core version 9.23.

I get some strange errors in my log when I try to incrementally update the Commerce Search index. I think I have located the problem to a bug in the ecf_CatalogEntrySearch_Init stored procedure.

I have tried to call the stored procedure manually from SQL Server Management Studio, and then tried to insert PRINT statements in the stored procedure to see what happends.

If the @DatabaseClockOffsetMS = 0, then the SQL statement below is generated. Notice the parts highlighted with bold.

I guess this part of the stored procedure is not executed:

if (isnull(@DatabaseClockOffsetMS, 0)> 0)


set @EarliestModifiedDate =DATEADD(MS,-@DatabaseClockOffsetMS, @EarliestModifiedDate)

set @EarliestModifiedFilter =' Modified >= cast('''+CONVERT(nvarchar(100), @EarliestModifiedDate, 127)+''' as datetime)'

set @LatestModifiedFilter =' and Modified <= cast('''+convert(nvarchar(100), @latestmodifieddate, 127)+''' as datetime)'>



Generated SQL statement:

select distinct '69BECD2C-7688-4838-B832-F50AC6DB70D2'

,ROW_NUMBER()over (orderby e.CatalogEntryId)

, e.CatalogEntryId

, e.ApplicationId

from CatalogEntry e

where e.ApplicationId ='A5390361-7309-45C4-8F6F-FD9DE6C9E5B4'and

e.CatalogId = 2 and

e.CatalogEntryId in

(select ObjectId

from CatalogContentEx

where ObjectTypeId = 0 and

Modified >=cast('2016-12-01T00:00:00'asdatetime)and

Modified <=cast('2016-12-17t00:00:00'asdatetime)>


select CatalogEntryId

from NodeEntryRelation

where and

Modified >=cast('2016-12-01T00:00:00'asdatetime)and

Modified <=cast('2016-12-17t00:00:00'asdatetime)>


select cast(ObjectKey asint)as CatalogEntryId

from ApplicationLog

where [Source] ='catalog'and

[Operation] ='Modified'and

[ObjectType] ='relation' and


Created >=cast('2016-12-01T00:00:00'asdatetime)and

Created <= cast('2016-12-17t00:00:00' as datetime))>

Dec 16, 2016 14:12


Sorry for the late reply - it's not until today when I can spend some time to read and understand the problem.

It sounds like a bug to me - I will file one so we can look at it. Thank you for bringing this to our attention.



Jan 12, 2017 10:31
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.