SQL command timeout when importing catalog.xml

Vote:
 

Epi commerce 9.16

We are using the inRiver integration to import the product catalog. When the catalog meta data in epi is clean and the catalog.xml is sent over to epi the catalog data gets imported and everyone is happy. But subsequent imports of the full catalog structure ends in SQL command timeouts. We have tried to decrease the catalogImportBatchSize and set the commandTimeout but with no luck. Any more ideas out there?

2016-06-23 03:32:43,323 [47] ERROR inRiver.EPiServerCommerce.Import.InriverDataImportController: Catalog Import Failed
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=1; handshake=15; [Login] initialization=0; authentication=0; [Post-Login] complete=1; ---> 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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command)
at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, DataParameter[] commandParameters)
at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, DataParameter[] commandParameters)
at Mediachase.Commerce.Catalog.Data.CatalogEntryAdmin.Save()
at Mediachase.Commerce.Catalog.Managers.CatalogEntryManager.SaveCatalogEntry(CatalogEntryDto dataset)
at Mediachase.Commerce.Catalog.ImportExport.CatalogImportExport.SaveEntryDto(CatalogEntryDto workingCatalogEntryDto, Dictionary`2 metaObjectsList, Dictionary`2 priceGroups, Dictionary`2 warehouseInventories)
at Mediachase.Commerce.Catalog.ImportExport.CatalogImportExport.ReadEntries(Guid applicationId, Int32 catalogId, XmlReader reader, String baseFilePath, Int32 totalCount, String defaultCurrency, Boolean overwrite, IEnumerable`1 catalogLanguages)
at Mediachase.Commerce.Catalog.ImportExport.CatalogImportExport.Import(Stream input, Guid applicationId, String baseFilePath, Boolean overwrite)
at inRiver.EPiServerCommerce.Import.InriverDataImportController.ImportCatalogXmlWithHandlers(Stream catalogXml, IEnumerable`1 catalogImportHandlers)
at inRiver.EPiServerCommerce.Import.InriverDataImportController.ImportCatalogXml(String path)
ClientConnectionId:1e33a6e3-68bb-4431-a86a-d686d6fd4b8e
Error Number:-2,State:0,Class:11
ClientConnectionId before routing:87a16f53-a5a8-4a75-858a-87672ec65a16
Routing Destination:b495ff6b49fe.tr16.eastus1-a.worker.database.windows.net,11016

#150585
Edited, Jun 23, 2016 7:22
Vote:
 

Hi,

That's very strange. How many entries do you have in the catalog? And how big is your metadata structure (number of metaclasses/metafields)?

I would suggest you to contact our developer support and provide them the catalog - we will take it from there.

/Q

#150645
Jun 27, 2016 9:45
Vote:
 

Hi Quan,

We have about 10000 entries and a basic meta model. I have worked with alot more complex meta models and bigger catalogs without ever seeing this problem. Support now has the catalog.xml and some SQL logs. 

#150696
Jun 28, 2016 0:22
Vote:
 

Did anyone get a resolution for this?  I'm having the same problem with Commerce 9.20

#152281
Aug 23, 2016 9:57
Vote:
 

Hi,

This issue is a specific problem to the combination of site/database and/or catalog, so there is no generic solution. I would suggest you to contact our developer support service - we will need to look into details to see what is wrong.

Regards,

/Q

#152282
Aug 23, 2016 10:02
Vote:
 

We upgraded inRiver to latest release and that seemed to have solved the problem. Did not touch the site/database or catalog. 

#154933
Sep 02, 2016 11:46
Vote:
 

I suggest upgrading, this is possibly related to a bug fixed in a later version (9.22.1).

#163352
Oct 20, 2016 13:59