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