We’ve uncovered an issue with the Build eCommerce Assortment Items scheduled task add-in (Fig. 1 - 2) where a SQL timeout error (Fig. 3-4) results in an empty assortment until the assortment is successfully re-built again either manually or at the next successful scheduled task run.
Our temporary workaround was to turn off the standard scheduled task and refactor as shown in the attached query sample (Query.txt) to look for deltas, but it’d be greatly appreciated if AssortmentRepository.cs could be adjusted to only impact deltas (updates) instead of completely clearing and rebuilding the assortments to avoid scenarios like this.
Figure 1
Figure 2
Figure 3
Error text from log:
2023-04-04 01:35:32.067: DELETE FROM [EcomAssortmentItems] WHERE [AssortmentItemAssortmentID] = @p0 AND [AssortmentItemLanguageID] = @p1; INSERT INTO EcomAssortmentItems SELECT @p0 as AssortmentItemAssortmentID, -1 as AssortmentItemRelationAutoID, '' as AssortmentItemRelationType, @p1 as AssortmentItemLanguageID, AllProducts.ProductID, AllProducts.ProductVariantID FROM ( SELECT DISTINCT EcomProducts.ProductID as ProductID, EcomProducts.ProductVariantID as ProductVariantID FROM EcomAssortmentShopRelations INNER JOIN EcomShopGroupRelation ON EcomShopGroupRelation.ShopGroupShopID = EcomAssortmentShopRelations.AssortmentShopRelationShopID INNER JOIN EcomGroupProductRelation ON EcomGroupProductRelation.GroupProductRelationGroupID = EcomShopGroupRelation.ShopGroupGroupID INNER JOIN EcomProducts ON EcomProducts.ProductID = EcomGroupProductRelation.GroupProductRelationProductID WHERE EcomAssortmentShopRelations.AssortmentShopRelationAssortmentID = @p0 AND EcomProducts.ProductLanguageId = @p1 UNION SELECT DISTINCT EcomProducts.ProductID as ProductID, EcomProducts.ProductVariantID as ProductVariantID FROM EcomAssortmentGroupRelations INNER JOIN EcomGroupProductRelation ON EcomGroupProductRelation.GroupProductRelationGroupID = EcomAssortmentGroupRelations.AssortmentGroupRelationGroupID INNER JOIN EcomProducts ON EcomProducts.ProductID = EcomGroupProductRelation.GroupProductRelationProductID WHERE EcomAssortmentGroupRelations.AssortmentGroupRelationAssortmentID = @p0 AND EcomProducts.ProductLanguageId = @p1 UNION SELECT EcomProducts.ProductID as ProductID, EcomProducts.ProductVariantID as ProductVariantID FROM EcomAssortmentProductRelations INNER JOIN EcomProducts ON EcomProducts.ProductID = EcomAssortmentProductRelations.AssortmentProductRelationProductID WHERE EcomAssortmentProductRelations.AssortmentProductRelationAssortmentID = @p0 AND EcomProducts.ProductLanguageId = @p1 ) as AllProducts. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at Dynamicweb.Data.Database.ExecuteScalar(IDbCommand command) ClientConnectionId:aba73855-9f1a-40e1-a814-9c9222929ed7 Error Number:-2,State:0,Class:11 ClientConnectionId before routing:e96729ca-b980-49ee-a0cc-68b0d6f5ded3 Routing Destination:a242d6fe0d13.tr18735.eastus2-a.worker.database.windows.net,11009
Figure 4
