You’re offline. This is a read only version of the page.
Hi!
We are trying to push N job (size 20MB) on the Retail stores but it gets stuck up (error Message attached). Although its a dedicated 10 MBps data circuit on Fiber Connectivity with less than 15 ms delay.
Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessTargetRequestHeaderException: ProcessTargetRequestHeader failed to execute all write requests. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessWriteRequestException: Write request on table:[ax].[INVENTTABLEMODULE] failed to execute. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.RunException: Run() failed while performing write operation on table. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.PerformWriteOperationException: Query: MERGE [ax].[INVENTTABLEMODULE] AS dst
USING (SELECT [ALLOCATEMARKUP],[ENDDISC],[INTERCOMPANYBLOCKED],[ITEMID],[LINEDISC],[MARKUP],[MARKUPGROUPID],[MAXIMUMRETAILPRICE_IN],[MODULETYPE],[MULTILINEDISC],[OVERDELIVERYPCT],[PRICE],[PRICEDATE],[PRICEQTY],[PRICEUNIT],[RECID],[SUPPITEMGROUPID],[TAXITEMGROUPID],[UNDERDELIVERYPCT],[UNITID],[DATAAREAID] FROM [#ax_INVENTTABLEMODULE_f150f2f0-55e8-4b75-8cbc-1617e9d8000b]) AS src
ON (dst.[ITEMID]=src.[ITEMID] AND dst.[MODULETYPE]=src.[MODULETYPE] AND dst.[DATAAREAID]=src.[DATAAREAID])
WHEN MATCHED THEN
UPDATE SET [ALLOCATEMARKUP]=src.[ALLOCATEMARKUP],[ENDDISC]=src.[ENDDISC],[INTERCOMPANYBLOCKED]=src.[INTERCOMPANYBLOCKED],[LINEDISC]=src.[LINEDISC],[MARKUP]=src.[MARKUP],[MARKUPGROUPID]=src.[MARKUPGROUPID],[MAXIMUMRETAILPRICE_IN]=src.[MAXIMUMRETAILPRICE_IN],[MULTILINEDISC]=src.[MULTILINEDISC],[OVERDELIVERYPCT]=src.[OVERDELIVERYPCT],[PRICE]=src.[PRICE],[PRICEDATE]=src.[PRICEDATE],[PRICEQTY]=src.[PRICEQTY],[PRICEUNIT]=src.[PRICEUNIT],[RECID]=src.[RECID],[SUPPITEMGROUPID]=src.[SUPPITEMGROUPID],[TAXITEMGROUPID]=src.[TAXITEMGROUPID],[UNDERDELIVERYPCT]=src.[UNDERDELIVERYPCT],[UNITID]=src.[UNITID]
WHEN NOT MATCHED THEN
INSERT ([ALLOCATEMARKUP],[ENDDISC],[INTERCOMPANYBLOCKED],[ITEMID],[LINEDISC],[MARKUP],[MARKUPGROUPID],[MAXIMUMRETAILPRICE_IN],[MODULETYPE],[MULTILINEDISC],[OVERDELIVERYPCT],[PRICE],[PRICEDATE],[PRICEQTY],[PRICEUNIT],[RECID],[SUPPITEMGROUPID],[TAXITEMGROUPID],[UNDERDELIVERYPCT],[UNITID],[DATAAREAID])
VALUES (src.[ALLOCATEMARKUP],src.[ENDDISC],src.[INTERCOMPANYBLOCKED],src.[ITEMID],src.[LINEDISC],src.[MARKUP],src.[MARKUPGROUPID],src.[MAXIMUMRETAILPRICE_IN],src.[MODULETYPE],src.[MULTILINEDISC],src.[OVERDELIVERYPCT],src.[PRICE],src.[PRICEDATE],src.[PRICEQTY],src.[PRICEUNIT],src.[RECID],src.[SUPPITEMGROUPID],src.[TAXITEMGROUPID],src.[UNDERDELIVERYPCT],src.[UNITID],src.[DATAAREAID]);
DROP TABLE [#ax_INVENTTABLEMODULE_f150f2f0-55e8-4b75-8cbc-1617e9d8000b];
---> System.Data.SqlClient.SqlException: 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection, SqlTransaction transaction)
--- End of inner exception stack trace ---
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection, SqlTransaction transaction)
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)
--- End of inner exception stack trace ---
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection, SqlTransaction transaction)
--- End of inner exception stack trace ---
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection, SqlTransaction transaction)
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)
--- End of inner exception stack trace ---
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)
at Microsoft.Dynamics.Retail.SynchClient.Core.DownloadAgent.ApplySessionFileToClientDatabase(SessionManager sessionMgr, String fileName)
*This post is locked for comments
Hi Haris,
The solution proposed by Irfan is possible. However, I consider it a little more intrusive, and it can make future upgrades difficult.
When I check this type of error:
---> System.Data.SqlClient.SqlException: 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
It is usually possible to resolve by doing the REBUILD ALL of the related table indexes in the channel database. (in this case, for example, ax.inventTableModule)
Disorganized index, can cause a very long time in SQL Server MERGE operation.
Be aware of performing index maintenance recursively. As well as performing synchronization (1040) frequently
Hi Irfan ul,
Can you please more elaborate point 2 and 3 this might help me to resolve the problem.
Regards
Haris Gillani
Dear can you please share the KB ID (Issue id) of this problem? i am trying to search it on LCS
Thanks!
Great post and provided solution work for me :)
Yes, it was one of the table RetailGroupLineMember which is not getting populated in our case correctly. Following is the solution we got from MICROSOFT.
SUMMARY OF TROUBLESHOOTING
=============================
We found that the performance bottleneck was due to a combination of the data volume of RetailGroupMemberLine table (in that case there’re 1M rows in the same table), and the way how RetailGroupMemberLine node is specified in the out-of-box Table Distribution.
The net result is that CDX queries contain link condition such as this:
RetailGroupMemberLine.Product = EcoResProduct.RecId OR RetailGroupMemberLine.Variant = EcoResProduct.RecId
As an example, you may find one in class RetailCDXChannelSpecificData_AX63, method d_RetailGroupMemberLine_109:
insert_recordset ref2 (RefTableId, RefRecId)
select tid, RecId
from t7
group by RecId
join t6
where ((t7.Product==t6.RecId) || (t7.Variant==t6.RecId))
join t5
where ((t6.RecId==t5.DistinctProductVariant))
The OR condition causes the CDX generated queries to run very slowly, given the data volume of RetailGroupMemberLine table rows.
The solution only involves configuration change on the AX side. No code or hotfix is required.
They are:
1. Customize the Table Distribution, by re-arranging RetailGroupMemberLine nodes in the XML definition. The net effect is that CDX generated query will not have the OR condition. This change has no negative effect. There will be no data loss. Full sync or delta sync will continue to work for this table. The exact procedures involved are:
a. Open Retail -> Setup -> Retail scheduler -> Retail channel schema
b. Pick AX 2012 R3
c. Click “Select all” button in “Retail data distribution” table, hit “Ctrl+C” to copy the content to clipboard.
d. Open any text editor and:
i. Paste the content from clipboard.
ii. Find nodes like below – there should be 2 finding results:
<Table name="RetailGroupMemberLine">
<LinkGroup>
<Link type="FieldMatch" fieldName="Product" parentFieldName="RecId" />
</LinkGroup>
<LinkGroup>
<Link type="FieldMatch" fieldName="Variant" parentFieldName="RecId" />
</LinkGroup>
<Table name="RetailSpecialCategoryMember">
<LinkGroup>
<Link type="FieldMatch" fieldName="RetailGroupMember" parentFieldName="RecId" />
</LinkGroup>
</Table>
</Table>
iii. Remove the highlighted section for field Variant.
iv. Hit “Ctrl+A” and then “Ctrl+C” to copy the whole changed content.
e. Back to Retail channel schema form, hit “Delete” button to clear the content, hit “Ctrl+V” to paste changed content.
f. “Ctrl+S” to save
g. Click “Generate classes” button
h. Make sure class generation completes without error (info log pops up as notice for IL generation completed)
execute the below statements at AX DB side .
create nonclustered index RETAILGROUPMEMBERLINE_index1
on RETAILGROUPMEMBERLINE (partition) include (PRODUCT ,RECID)
create nonclustered index RETAILGROUPMEMBERLINE_index2
on RETAILGROUPMEMBERLINE (PRODUCT ,partition) include (RECID)
Hi Dear,
Did you find a solution for this issue please ?
Regards,
Rakan
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,228
Super User 2024 Season 2
#2
Martin Dráb
230,056
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (