AI OnAI Off
SQL command timeout when importing catalog.xml
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
Jun 27, 2016 9:45
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.
Jun 28, 2016 0:22
Did anyone get a resolution for this? I'm having the same problem with Commerce 9.20
Aug 23, 2016 9:57
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
Aug 23, 2016 10:02
We upgraded inRiver to latest release and that seemed to have solved the problem. Did not touch the site/database or catalog.
Sep 02, 2016 11:46
I suggest upgrading, this is possibly related to a bug fixed in a later version (9.22.1).
Oct 20, 2016 13:59
Hello Episerver
This thread is old but we have currently exactly the same problem with a full publish of our production data into a test environment. Strangely production does not seem to have this problem but our test environment does.
The catalog.20200513-120603.179.zip that is being imported is 8Mb and contains a xml file of 154 Mb.
Our Episerver is a newer then 9.22.1 (commerce is currently version 12.16)
Do you have suggestions how to set the command timeout higher?
Kind regards
Remco
Stacktrace:
2020-05-27T12:56:19,Error,akzo02upgrdr70u1inte,b17cc9,637261809794292178,0,7936,26,"inRiver.EPiServerCommerce.Twelve.Importer.InriverDataImportController : Catalog Import Failed for path - catalog.20200513-120603.179.zip with exception -System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated. ---> 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, 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.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
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.Commerce.Catalog.CatalogContent.CatalogContentSync.DeleteAllVersions(Int32[] objectIds, CatalogContentType catalogContentType)
at Mediachase.Commerce.Catalog.Data.CatalogEntryAdmin.Save()
at Mediachase.Commerce.Catalog.Managers.CatalogEntryManager.SaveCatalogEntry(CatalogEntryDto dataset)
at Mediachase.Commerce.Catalog.ImportExport.Import.EntriesCommitter.SaveDto(EntrySaveInfo entrySaveInfo)
at Mediachase.Commerce.Catalog.ImportExport.Import.EntriesCommitter.Save(EntrySaveInfo entrySaveInfo)
at Mediachase.Commerce.Catalog.ImportExport.Import.EntriesReader.Read()
at Mediachase.Commerce.Catalog.ImportExport.Import.Importer.Import(Stream stream, String sourceDirectory, Boolean overwrite)
at inRiver.EPiServerCommerce.Twelve.Importer.InriverDataImportController.ImportCatalogXml(Stream catalogXmlStream) in C:\iPMC.Connectors\iPMC-EPiServer\inRiver.EPiServerCommerce.Twelve.Importer\InriverDataImportController.cs:line 991
at inRiver.EPiServerCommerce.Twelve.Importer.InriverDataImportController.<>c__DisplayClass34_0.b__0() in C:\iPMC.Connectors\iPMC-EPiServer\inRiver.EPiServerCommerce.Twelve.Importer\InriverDataImportController.cs:line 629
ClientConnectionId:0a8cc9ef-fac8-4440-a30e-209e8d5f7165
Error Number:-2,State:0,Class:11
ClientConnectionId before routing:1bbfbbee-e850-4c72-a43b-b400e282c09e
Routing Destination:d39ebb263d17.tr21.northeurope1-a.worker.database.windows.net,11142
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated. ---> 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, 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.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
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.Commerce.Catalog.CatalogContent.CatalogContentSync.DeleteAllVersions(Int32[] objectIds, CatalogContentType catalogContentType)
at Mediachase.Commerce.Catalog.Data.CatalogEntryAdmin.Save()
at Mediachase.Commerce.Catalog.Managers.CatalogEntryManager.SaveCatalogEntry(CatalogEntryDto dataset)
at Mediachase.Commerce.Catalog.ImportExport.Import.EntriesCommitter.SaveDto(EntrySaveInfo entrySaveInfo)
at Mediachase.Commerce.Catalog.ImportExport.Import.EntriesCommitter.Save(EntrySaveInfo entrySaveInfo)
at Mediachase.Commerce.Catalog.ImportExport.Import.EntriesReader.Read()
at Mediachase.Commerce.Catalog.ImportExport.Import.Importer.Import(Stream stream, String sourceDirectory, Boolean overwrite)
at inRiver.EPiServerCommerce.Twelve.Importer.InriverDataImportController.ImportCatalogXml(Stream catalogXmlStream) in C:\iPMC.Connectors\iPMC-EPiServer\inRiver.EPiServerCommerce.Twelve.Importer\InriverDataImportController.cs:line 991
at inRiver.EPiServerCommerce.Twelve.Importer.InriverDataImportController.<>c__DisplayClass34_0.b__0() in C:\iPMC.Connectors\iPMC-EPiServer\inRiver.EPiServerCommerce.Twelve.Importer\InriverDataImportController.cs:line 629
ClientConnectionId:0a8cc9ef-fac8-4440-a30e-209e8d5f7165
Error Number:-2,State:0,Class:11
ClientConnectionId before routing:1bbfbbee-e850-4c72-a43b-b400e282c09e
Routing Destination:d39ebb263d17.tr21.northeurope1-a.worker.database.windows.net,11142",
May 27, 2020 13:56
It is most likely that your prod configuration is (much) higher than your prep, making it's less prone to the error. the issue is caused as SQL Server can't process a SP in time (by default, 30s)
May 27, 2020 14:18
Hi Quan
Thanks for your reply.
Yes, that is our conclusion as well. But now we cannot debug production problems in test.
So we are looking for a way to actually process this imports.
Is upscaling the Test Azure databases then the only posible solution?
Kind regards
Remco
May 27, 2020 16:05
I would think so,
However it might be worth checking if ecfVersion_DeleteByObjectIds in both prod and prep match each other. We improved it in 11.2.6 but there is chance, even thought unlikely, that your prep is an old version = less effective
May 27, 2020 16:12
Hi Quan
ecfVersion_DeleteByObjectIds is the same on all environments, and the newer version (https://world.episerver.com/documentation/Release-Notes/ReleaseNote/?releaseNoteId=COM-5505) so thats ok.
Eventually upscaling the database solved the problem or us.
Not ideal but workable.
Kind regards
Remco
May 29, 2020 7:33
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