Error:- Taking too much time and geting error Execution Timeout Expired during the sync data (run jobs Dynamics Ax Retail)
------------------------------------------------------------------------------------------------------------------------
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) 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) 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() --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Microsoft.Dynamics.AX.ManagedInterop.ClrBridgeImpl.InvokeClrInstanceMethod(ClrBridgeImpl* , ObjectWrapper* objectWrapper, Char* pszMethodName, Int32 argsLength, ObjectWrapper** arguments, Boolean* argsAreByRef, Boolean* isException)
--------------------------------------------------------------------------------------------------------------------
Solution Step Checks:-
--------------------------------------------------------------------------------------------------------------------
1. Check for Missing Index in Async Server Mesage database.
2. Delete old download sessions.
3. Always run the process in batch. Recursively (Decreasing the row of records to update).
4. Retail > Setup > Parameters > Retail Scheduler Parameters > Monitoring > Retention Period in Days
This parameter conditions the days for retaining monitoring data.
----------------------------------------------------------------------------------------------------------------------
SQL SERVER Qurery:-
----------------------------------------------------------------------------------------------------------------------
USE AsyncServerHQ
DELETE FROM DOWNLOADSESSION WHERE JOBID LIKE '%1%'
DELETE FROM DOWNLOADSESSIONDATASTORE WHERE MESSAGE LIKE '%FAIL%'
DELETE FROM DOWNLOADSESSIONDATASTORE WHERE MESSAGE LIKE '%APP%'
TRUNCATE TABLE DOWNLOADSESSIONDATASTORELOG
TRUNCATE TABLE UPLOADSESSION
TRUNCATE TABLE UPLOADSESSIONLOG
Use MicrosoftDynamicsAX
TRUNCATE TABLE [dbo].[RETAILCDXDOWNLOADSESSION]
TRUNCATE TABLE [dbo].[RETAILCDXDOWNLOADSESSIONDATASTORE]
TRUNCATE TABLE [dbo].[RETAILCDXDOWNLOADSESSIONDATASTORELOG]
TRUNCATE TABLE [dbo].[RETAILCDXUPLOADSESSION]
TRUNCATE TABLE [dbo].[RETAILCDXUPLOADSESSIONLOG]
---------------------------------------------------------------------------------------------------------------------
AX AOT
---------------------------------------------------------------------------------------------------------------------
If Problem does not solved by then ==> Modify Class > RetailCDXMonDataSync, Method > Run, add property > cmd.set_CommandTimeout([NewTimeout]); in object (cmd = new System.Data.SqlClient.SqlCommand) (30 seconds is default value in System.Data.SqlClient)
---------------------------------------------------------------------------------------------------------------------
SQL SERVER
----------------------------------------------------------------------------------------------------------------------
Unless you specify otherwise using the SqlCommand.CommandTimeout property, the .Net SqlClient Data Provider will wait no more than 30 seconds before cancelling the query and raising a timeout error. You can set the CommandTimeout property to specify a longer value (in seconds) if a long-running query is normal and expected.
Long-running queries can be a symptom of the need for query and index tuning or blocking. Examine the query plan in SQL Server Management Studio by pressing CTRL-L with the query in a query window to make sure indexes are used efficiently, additional indexes are needed or query tuning is needed. A quick way to identify blocking is by running sp_who2 while the query is running to see if BlkBy contains a SPID of a blocking session.
--------------------------------------------------------------------------------------------------------------------------
If Items not Showing in POS and no Error in Sync Services (CDX)
--------------------------------------------------------------------------------------------------------------------------
Run below Query into Store Database
Alter Table ECORESPRODUCTTRANSLATION Column Name nvarchar(99) Not Null
--------------------------------------------------------------------------------------------------------------------------
If Item Price not Showing in POS and no Error in Sync Services (CDX)
--------------------------------------------------------------------------------------------------------------------------
Check Mark on below
1. Retail >Retail Parameter >Discount Tab >Check Mark on Use Default Pricing Engine for line item calculation
2. Sales and Marketing >Setup> Pricing/Discount >Active Price / Discount
------------------------------------------------------------------------------------------------------------------------
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) 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) 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() --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Microsoft.Dynamics.AX.ManagedInterop.ClrBridgeImpl.InvokeClrInstanceMethod(ClrBridgeImpl* , ObjectWrapper* objectWrapper, Char* pszMethodName, Int32 argsLength, ObjectWrapper** arguments, Boolean* argsAreByRef, Boolean* isException)
--------------------------------------------------------------------------------------------------------------------
Solution Step Checks:-
--------------------------------------------------------------------------------------------------------------------
1. Check for Missing Index in Async Server Mesage database.
2. Delete old download sessions.
3. Always run the process in batch. Recursively (Decreasing the row of records to update).
4. Retail > Setup > Parameters > Retail Scheduler Parameters > Monitoring > Retention Period in Days
This parameter conditions the days for retaining monitoring data.
----------------------------------------------------------------------------------------------------------------------
SQL SERVER Qurery:-
----------------------------------------------------------------------------------------------------------------------
USE AsyncServerHQ
DELETE FROM DOWNLOADSESSION WHERE JOBID LIKE '%1%'
DELETE FROM DOWNLOADSESSIONDATASTORE WHERE MESSAGE LIKE '%FAIL%'
DELETE FROM DOWNLOADSESSIONDATASTORE WHERE MESSAGE LIKE '%APP%'
TRUNCATE TABLE DOWNLOADSESSIONDATASTORELOG
TRUNCATE TABLE UPLOADSESSION
TRUNCATE TABLE UPLOADSESSIONLOG
Use MicrosoftDynamicsAX
TRUNCATE TABLE [dbo].[RETAILCDXDOWNLOADSESSION]
TRUNCATE TABLE [dbo].[RETAILCDXDOWNLOADSESSIONDATASTORE]
TRUNCATE TABLE [dbo].[RETAILCDXDOWNLOADSESSIONDATASTORELOG]
TRUNCATE TABLE [dbo].[RETAILCDXUPLOADSESSION]
TRUNCATE TABLE [dbo].[RETAILCDXUPLOADSESSIONLOG]
---------------------------------------------------------------------------------------------------------------------
AX AOT
---------------------------------------------------------------------------------------------------------------------
If Problem does not solved by then ==> Modify Class > RetailCDXMonDataSync, Method > Run, add property > cmd.set_CommandTimeout([NewTimeout]); in object (cmd = new System.Data.SqlClient.SqlCommand) (30 seconds is default value in System.Data.SqlClient)
---------------------------------------------------------------------------------------------------------------------
SQL SERVER
----------------------------------------------------------------------------------------------------------------------
Unless you specify otherwise using the SqlCommand.CommandTimeout property, the .Net SqlClient Data Provider will wait no more than 30 seconds before cancelling the query and raising a timeout error. You can set the CommandTimeout property to specify a longer value (in seconds) if a long-running query is normal and expected.
Long-running queries can be a symptom of the need for query and index tuning or blocking. Examine the query plan in SQL Server Management Studio by pressing CTRL-L with the query in a query window to make sure indexes are used efficiently, additional indexes are needed or query tuning is needed. A quick way to identify blocking is by running sp_who2 while the query is running to see if BlkBy contains a SPID of a blocking session.
--------------------------------------------------------------------------------------------------------------------------
If Items not Showing in POS and no Error in Sync Services (CDX)
--------------------------------------------------------------------------------------------------------------------------
Run below Query into Store Database
Alter Table ECORESPRODUCTTRANSLATION Column Name nvarchar(99) Not Null
--------------------------------------------------------------------------------------------------------------------------
If Item Price not Showing in POS and no Error in Sync Services (CDX)
--------------------------------------------------------------------------------------------------------------------------
Check Mark on below
1. Retail >Retail Parameter >Discount Tab >Check Mark on Use Default Pricing Engine for line item calculation
2. Sales and Marketing >Setup> Pricing/Discount >Active Price / Discount
