N job getting Stuck Up

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Dear,

Did you find a solution for this issue please ?

Regards,

Rakan

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (2)

Report
Copy link

Link copied!

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)

Was this reply helpful? Yes No
Faiqa Khushi 915 on at

Like (0)

Report
Copy link

Link copied!

Great post and provided solution work for me :)

Was this reply helpful? Yes No
Haris Hussain on at

Like (0)

Report
Copy link

Link copied!

Dear can you please share the KB ID (Issue id) of this problem? i am trying to search it on LCS

Thanks!

Was this reply helpful? Yes No
Harisgillani 291 on at

Like (0)

Report
Copy link

Link copied!

Hi Irfan ul,

Can you please more elaborate point 2 and 3 this might help me to resolve the problem.

Regards

Haris Gillani

Was this reply helpful? Yes No
Suggested answer

Rodolfo Recalde on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No