Have you tried to index data using Job provided in CMS Admin?

Regards
/K

Hi,

Do you have anything in log file? Like stacktrace, etc?

Hi,

The error occurs in the stored procedure "ecf_CatalogEntrySearch_Init" when there is no clock offset between the database server and application server.

You can fix the error by moving the set of @EarliestModifiedFilter and @LatestModifiedFilter outside the null check statement of @DatabaseClockOffsetMS.

 -- adjust modified date filters to account for clock difference between database server and application server clocks
if (isnull(@DatabaseClockOffsetMS, 0) > 0)
begin
set @EarliestModifiedDate = DATEADD(MS, -@DatabaseClockOffsetMS, @EarliestModifiedDate)
end

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

Regards,

Dominique

The suggestion from Dominique sounds reasonable. However it's worth noting that this issue has been fixed in newer versions - so please update if you can 

Our current version of EPiServer.Commerce.Core is 10.4.1 but we've noticed some inconsistency in the sql update scripts.

The full EPiServer.Commerce.Core.sql has the following clock offset fix:

-- adjust modified date filters to account for clock difference between database server and application server clocks
if (isnull(@DatabaseClockOffsetMS, 0) > 0)
begin
set @EarliestModifiedFilterPadded = ' Modified >= cast(''' + CONVERT(nvarchar(100), DATEADD(MS, -@DatabaseClockOffsetMS, @EarliestModifiedDate), 127) + ''' as datetime)'
set @LatestModifiedFilterPadded = ' Modified <= cast(''' + CONVERT(nvarchar(100), DATEADD(MS, -@DatabaseClockOffsetMS, @LatestModifiedDate), 127) + ''' as datetime)'
end

But the latest altering of ecf_CatalogEntrySearch_Init is found in 7.0.7.0.sql with the following fix:

-- adjust modified date filters to account for clock difference between database server and application server clocks
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

Regards,

Dominique

That's very strange. The fix I mentioned was included in 10.4.0. Here's what it looks like now:

create procedure [dbo].[ecf_CatalogEntrySearch_Init]
    @CatalogId int,
    @SearchSetId uniqueidentifier,
    @IncludeInactive bit,
    @EarliestModifiedDate datetime = null,
    @LatestModifiedDate datetime = null,
    @DatabaseClockOffsetMS int = null
as
begin
	declare @purgedate datetime
	begin try
		set @purgedate = datediff(day, 3, GETUTCDATE())
		delete from [CatalogEntrySearchResults_SingleSort] where Created < @purgedate
	end try
	begin catch
	end catch

    declare @ModifiedCondition nvarchar(max)
    declare @EarliestModifiedFilter nvarchar(4000) = ''
	declare @LatestModifiedFilter nvarchar(4000) = ''
	declare @query nvarchar(max)
	declare @AppLogQuery nvarchar(4000)

	set @ModifiedCondition = 'select ObjectId from CatalogContentEx where ObjectTypeId = 0'
    
    -- @ModifiedFilter: if there is a filter, build the where clause for it here.
    if (@EarliestModifiedDate is not null)
    begin
	  	set @EarliestModifiedFilter = ' Modified >= cast(''' + CONVERT(nvarchar(100), @EarliestModifiedDate, 127) + ''' as datetime)'
	  	set @ModifiedCondition = @ModifiedCondition + ' and ' + @EarliestModifiedFilter
	end
    if (@LatestModifiedDate is not null)
    begin
    	set @LatestModifiedFilter = ' Modified <= cast('''  + CONVERT(nvarchar(100), @LatestModifiedDate, 127) + ''' as datetime)'
	 	set @ModifiedCondition = @ModifiedCondition + ' and ' + @LatestModifiedFilter
	end

    -- find all the catalog entries that have modified relations in NodeEntryRelation, or deleted relations in ApplicationLog
    if (@EarliestModifiedDate is not null and @LatestModifiedDate is not null)
    begin

		set @EarliestModifiedFilter = ' Modified >= cast(''' + CONVERT(nvarchar(100), @EarliestModifiedDate, 127) + ''' as datetime)'
		set @LatestModifiedFilter = ' Modified <= cast('''  + CONVERT(nvarchar(100), @LatestModifiedDate, 127) + ''' as datetime)'
		
		declare @EarliestModifiedFilterPadded nvarchar(4000) =  @EarliestModifiedFilter
		declare @LatestModifiedFilterPadded nvarchar(4000) = @LatestModifiedFilter

        -- adjust modified date filters to account for clock difference between database server and application server clocks    
        if (isnull(@DatabaseClockOffsetMS, 0) > 0)
        begin
            set @EarliestModifiedFilterPadded = ' Modified >= cast(''' + CONVERT(nvarchar(100), DATEADD(MS, -@DatabaseClockOffsetMS, @EarliestModifiedDate), 127) + ''' as datetime)'
			set @LatestModifiedFilterPadded = ' Modified <= cast('''  + CONVERT(nvarchar(100), DATEADD(MS, -@DatabaseClockOffsetMS, @LatestModifiedDate), 127) + ''' as datetime)'
		end

		-- applying the NodeEntryRelation.
		set @ModifiedCondition = @ModifiedCondition + ' union select CatalogEntryId from NodeEntryRelation where ' + @EarliestModifiedFilterPadded + ' and ' + @LatestModifiedFilterPadded
	
		set @EarliestModifiedFilter = REPLACE( @EarliestModifiedFilter, 'Modified', 'Created')
		set @LatestModifiedFilter = REPLACE( @LatestModifiedFilter, 'Modified', 'Created')
			
		set @AppLogQuery = ' union select cast(ObjectKey as int) as CatalogEntryId from ApplicationLog where [Source] = ''catalog'' and [Operation] = ''Modified'' and [ObjectType] = ''relation'' and ' + @EarliestModifiedFilter + ' and ' + @LatestModifiedFilter

		-- applying the ApplicationLog.
		set @ModifiedCondition = @ModifiedCondition + @AppLogQuery
    end

    set @query = 'insert into CatalogEntrySearchResults_SingleSort (SearchSetId, ResultIndex, CatalogEntryId) ' +
    'select distinct ''' + cast(@SearchSetId as nvarchar(36)) + ''', ROW_NUMBER() over (order by e.CatalogEntryId), e.CatalogEntryId from CatalogEntry e ' +
    ' inner join (' + @ModifiedCondition + ') o on e.CatalogEntryId = o.ObjectId' + 
	' where e.CatalogId = ' + cast(@CatalogId as nvarchar) + ' ' 	 
      
    if @IncludeInactive = 0 set @query = @query + ' and e.IsActive = 1'

    execute dbo.sp_executesql @query
    
    select @@ROWCOUNT
end

These changes are not applied in the versioned sql files which can be found in the EPiServer.Commerce.Core.10.4.1 NuGet package (EPiServer.Commerce.Core.10.4.1\tools\epiupdates_commerce\sql).

Is the fix added in a sql file which is not included in the package?

I investigated and you are correct, the update was in 7.8.0.0.sql which was released in 10.5.1. The SP was changed in 10.4.0 as I mentioned. I don't know why this happened, but we are looking into this.

You might upgrade to 10.5.1, or update the SP manually. I would recommend the former approach.