Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

N job getting Stuck Up

Posted on by Microsoft Employee

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

  • Suggested answer
    RE: N job getting Stuck Up

    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

  • Harisgillani Profile Picture
    Harisgillani 291 on at
    RE: N job getting Stuck Up

    Hi Irfan ul,

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

    Regards

    Haris Gillani

  • RE: N job getting Stuck Up

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

    Thanks!

  • Faiqa Khushi Profile Picture
    Faiqa Khushi 915 on at
    RE: AX 2012 R3 CU08 - N job getting Stuck Up

    Great post and provided solution work for me :)

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AX 2012 R3 CU08 - N job getting Stuck Up

    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)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AX 2012 R3 CU08 - N job getting Stuck Up

    Hi Dear,

    Did you find a solution for this issue please ?

    Regards,

    Rakan

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templates⚡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans