Finance | Project Operations, Human Resources, ...

LogisticsPostalAddress table update

(1) Share

Report

Posted on by dark_knight

936

Hello,

I wrote a programmatic query which is translated to the following T-SQL

Query  object 907cdf0: 
SELECT AccountNum, SalesDistrictId 
	FROM CustTable(CustTable_1) 
JOIN RecId FROM DirPartyTable(DirPartyTable_1) 
	ON CustTable.Party = DirPartyTable.RecId 
JOIN RecId FROM DirPartyLocation(DirPartyLocation_1) 
	ON DirPartyTable.RecId = DirPartyLocation.Party 
JOIN RecId FROM LogisticsLocation(LogisticsLocation_1) 
	ON DirPartyLocation.Location = LogisticsLocation.RecId 
JOIN Address, SalesDistrictId_WOOM FROM LogisticsPostalAddress(LogisticsPostalAddress_1) 
	ON LogisticsLocation.RecId = LogisticsPostalAddress.Location}	Dynamics.AX.Application.Query

then I traverse through the record and try to update the LogisticsPostalAddress table like that

 while (queryRun.next())
 {
     try
     {
         CustTable custTable = queryRun.get(tableNum(CustTable));

         if(custTable.SalesDistrictId)
         {
             ttsbegin;
               
             LogisticsPostalAddress logisticsPostalAddress = queryRun.get(tableNum(LogisticsPostalAddress));

             info(strFmt("@SYS70279" + " " + "@SYS135456" +" %2", custTable.AccountNum, logisticsPostalAddress.Address));

             logisticsPostalAddress.selectForUpdate(true);
             logisticsPostalAddress.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);

             logisticsPostalAddress.SalesDistrictId = custTable.SalesDistrictId;
                    
             logisticsPostalAddress.doUpdate();
                
             ttscommit;
         }
     }
     catch
     {
         exceptionTextFallThrough();
     }

I constantly get the error like this in FO on the doUpdate() line

I trued to check the locked transaction on the DB level by running the following t-sql script

-- List all Locks of the Current Database 
SELECT TL.resource_type AS ResType 
      ,TL.resource_description AS ResDescr 
      ,TL.request_mode AS ReqMode 
      ,TL.request_type AS ReqType 
      ,TL.request_status AS ReqStatus 
      ,TL.request_owner_type AS ReqOwnerType 
      ,TAT.[name] AS TransName 
      ,TAT.transaction_begin_time AS TransBegin 
      ,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura 
      ,ES.session_id AS S_Id 
      ,ES.login_name AS LoginName 
      ,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName 
      ,PARIDX.name AS IndexName 
      ,ES.host_name AS HostName 
      ,ES.program_name AS ProgramName 
FROM sys.dm_tran_locks AS TL 
     INNER JOIN sys.dm_exec_sessions AS ES 
         ON TL.request_session_id = ES.session_id 
     LEFT JOIN sys.dm_tran_active_transactions AS TAT 
         ON TL.request_owner_id = TAT.transaction_id 
            AND TL.request_owner_type = 'TRANSACTION' 
     LEFT JOIN sys.objects AS OBJ 
         ON TL.resource_associated_entity_id = OBJ.object_id 
            AND TL.resource_type = 'OBJECT' 
     LEFT JOIN sys.partitions AS PAR 
         ON TL.resource_associated_entity_id = PAR.hobt_id 
            AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') 
     LEFT JOIN sys.objects AS PAROBJ 
         ON PAR.object_id = PAROBJ.object_id 
     LEFT JOIN sys.indexes AS PARIDX 
         ON PAR.object_id = PARIDX.object_id 
            AND PAR.index_id = PARIDX.index_id 
WHERE TL.resource_database_id  = DB_ID() 
      AND ES.session_id <> @@Spid -- Exclude "my" session 
      -- optional filter  
      AND TL.request_mode <> 'S' -- Exclude simple shared locks 
ORDER BY TL.resource_type 
        ,TL.request_mode 
        ,TL.request_type 
        ,TL.request_status 
        ,ObjectName 
        ,ES.login_name;

it returned nothing.

How can I debug this issue please?

Thank you.

Categories:

Dynamics 365 Finance

Microsoft Dynamics AX

I have the same question (0)

All responses (5)

Answers (0)

Sohaib Cheema 48,219 User Group Leader on at

Like (0)

Report

LogisticsPostalAddress table update

Hi @dark_knight

Could it be that your record is never selected for the update in the first place. If you look at the standard examples, it suggest selecting record with keyword validtimestate

such as

select validtimestate(DateFrom,DateTo) * from FMVehicleRateEntity; //from date-todate range

select validtimestate(DateFrom) * from FMVehicleRateEntity; // as of date

On the query class you also have methods like query.validTimeStateDateRange(

and that could be mentioned before While(qr.next())

The 4 methods on the query class:

Example of update: https://learn.microsoft.com/en-us/previous-versions/dynamicsax-2012/developer/effects-of-valid-time-state-tables-on-read-and-write-operations?redirectedfrom=MSDN#update

Was this reply helpful? Yes No
dark_knight 936 on at

Like (0)

Report
LogisticsPostalAddress table update

Hi Andre,

No. It fetches different records with different recids. I checked that out.

Hi, Sohaib Cheema

Please take a look at my code. I use

logisticsPostalAddress.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);

Was this reply helpful? Yes No
Suggested answer

Sohaib Cheema 48,219 User Group Leader on at

Like (0)

Report

LogisticsPostalAddress table update

Hi @dark_knight

you are dealing with a table that has Valid Time State, please use TableObject.ValidTimeStateUpdateMode(ValidTimeStateUpdate::Correction)

before you update the record.

Was this reply helpful? Yes No
Suggested answer

CA Neeraj Kumar 1,599 on at

Like (0)

Report

LogisticsPostalAddress table update

Hi @dark_knight,

I have a question why you are trying to update the LogisticsPostalAddress table instead of inserting a new record. As when we create from front end or even update any field, it creates new record with Valid from datetime, so instead of updating, please go with inserting a new record.

If this helped, please mark it as "Verified" for others facing the same issue Keep in mind that it is possible to mark more than one answer as verified

Regards,

Neeraj Kumar

Was this reply helpful? Yes No
André Arnaud de Cal... 298,168 Super User 2025 Season 2 on at

Like (0)

Report

LogisticsPostalAddress table update

Hi Dark Knight,

Have you use the Visual Studio Debugger to check at what record it is failing? Maybe somehow it can find the same record twice?

Was this reply helpful? Yes No