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)
from CatalogEntry e
where e.ApplicationId ='A5390361-7309-45C4-8F6F-FD9DE6C9E5B4'and
e.CatalogId = 2 and
where ObjectTypeId = 0 and
select cast(ObjectKey asint)as CatalogEntryId
where [Source] ='catalog'and
[ObjectType] ='relation' and
Created <= cast('2016-12-17t00:00:00' as datetime))>=>
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.