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.