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)

begin

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)'>

end

-----

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)>

unionall

select CatalogEntryId

from NodeEntryRelation

where and

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

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

unionall

select cast(ObjectKey asint)as CatalogEntryId

from ApplicationLog

where [Source] ='catalog'and

[Operation] ='Modified'and

[ObjectType] ='relation' and

and

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

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