Developer forum

Hi Brian,

it looks like you have a lot of Item relations lists data in the database, could you try to increase the database command timeout by changing the value in the global settings path:
/GlobalSettings/System/Database/CommandTimeout? 
The max allowed value there is 240 (which is in seconds).
Kind regards, Dmitrij

Hi Dmitriy,

Thanks for the suggestion.  I changed the CommandTimeout to 240 and restarted the site.  I reran the Data Integration activity, and it failed with the same error.  Here is the contents of the log file:

2021-02-24 10:47:56.574: Starting job - BL Import Publications 2.
2021-02-24 10:48:00.239: Start checking input files changing
2021-02-24 10:48:05.248: Finish checking input files changing
2021-02-24 10:48:05.248: reading configuration
2021-02-24 10:48:05.575: Starting reading item type Publications.
2021-02-24 10:48:07.275: Finished reading item type Publications.
2021-02-24 11:32:07.957: Starting import item type Publications
2021-02-24 11:32:07.997: Job Failed with the following message: The operation is not valid for the state of the transaction.
   at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction, Guid promoterType)    at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType)    at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)    at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)    at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)    at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)    at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)    at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)    at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)    at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)    at System.Data.SqlClient.SqlConnection.Open()    at Dynamicweb.Data.DatabaseConnectionProvider.CreateConnection(Boolean open)    at Dynamicweb.Data.Database.CreateConnection()    at Dynamicweb.Content.Items.Queries.Repository.Update(IEnumerable`1 items, ItemContext context, Boolean synchronizePages)    at Dynamicweb.DataIntegration.Providers.ItemDestinationWriter.UpdateItems(String itemType, List`1 items)    at Dynamicweb.DataIntegration.Providers.ItemDestinationWriter.ImportItems()    at Dynamicweb.DataIntegration.Providers.ItemProvider.ItemProvider.RunJob(Job job)
2021-02-24 11:32:08.017: Job failed.
2021-02-24 11:32:08.017: Finished job - BL Import Publications 2.
2021-02-24 11:32:08.032: Batch failed.

I should also mention that this was working previous to upgrading from Dynamicweb version 8 to 9.9.7

Thanks

Hi Brian,
the error is the same but the stack trace shows a bit different and  now it failed even before it has got till the DeleteMissing, since UpdateItems method is called first and now it has
failed there. Can you try to go to your .Net folder and edit the machine.config file and add the following to the end of the configuration section.  That is, right before the </configuration> closing tag:

<configuration>
<system.transactions>

   <machineSettings maxTimeout="02:00:00" />

</system.transactions> 
</configuration>

It looks like some stuff in the Items api has changed since Dynamicweb 8.

Kind regards, Dmitrij

Hi Dmitriy,

That worked!  The Data Integration activity was able to complete.  I will let the customer do some more testing but that seems to be the answer.

Thanks for your help!

Hi Dmitriy,

Do you have any idea what the upper size limit will be for processing a file of this type?  Will this process stop working if the customer's import file continues to grow?

Hi Brian,
I have no idea, it is an Items api that causes the problems, so I do not have any clue what could be a limit for that.
Currently the process of updating the Items splits them into a chunks of 1000 and process them one by one in one transaction scope,
so it should work until the database query or trasaction timeout allows.
Kind regards, Dmitrij