London Dev Meetup Rescheduled! Due to unavoidable reasons, the event has been moved to 21st May. Speakers remain the same—any changes will be communicated. Seats are limited—register here to secure your spot!

Find indexing error Commerce Manager

Vote:
 

Hi!


When I'm trying to build the index in Commerce Manager I'm getting the following error:

"Build Failed using "catalog" indexer. "Incorrect syntax near the keyword 'and'. Incorrect syntax near the keyword 'and'"

Mediachase.Search.config looks like this:


...
 
serviceUrl="https://es-eu-dev-api01.episerver.net/21321321312" defaultIndex="index_name"/>
...

and



I used the following information: http://world.episerver.com/documentation/Items/Developers-Guide/Episerver-Commerce/9/Search/Configuring-Episerver-Find-search-provider/

Has anyone else seen this error?

Thanks!

/Mark

#178644
May 16, 2017 16:47
Vote:
 

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

Regards
/K

#178648
May 16, 2017 17:21
Vote:
 

Hi,

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

#178666
May 17, 2017 9:54
Vote:
 

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

#179236
Jun 06, 2017 11:45
Vote:
 

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 

#179240
Jun 06, 2017 12:10
Vote:
 

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

#179241
Jun 06, 2017 12:37
Vote:
 

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
#179243
Jun 06, 2017 13:54
Vote:
 

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?

#179244
Jun 06, 2017 14:34
Vote:
 

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.

#179270
Jun 07, 2017 10:08
* 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.