salesparmtable contains old transactions with parmjobstatus still at waiting

(0) Share

Report

Posted on by Steeve

145

Hi everybody,

Is there a way to clean up the transactions in salesparmtable and salesparmline tables. What is the impact of deleting these old records?

Thank you in advance.

Steeve

*This post is locked for comments

All responses (9)

Answers (0)

Christiaan 380 on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Hi Andre, I came across this article and it closely affects a client of mine. I have a couple of questions surrounding the use and affect of the purge (SQL) below this thread:

1. Restart updates; what AX process is meant here? Does the existing invoice Batch not do this already.

2. Most of the records are in waiting status for this client. do you know the meaning of that? the SQL seems to purge those out as well.

These tables are the largest for this client and it includes some custom indexes which grow with it as records get added. it will have a large impact on the Invoice batch processing with multi-threading turn on
Community Member Microsoft Employee on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Hi,

I suggest to delete records with SQL script because if you have milions of record, you can create some problems with Log. AX job do the deleting with one big transaction.

DELETE
FROM SALESPARMTABLE
WHERE SALESPARMTABLE.CREATEDDATETIME <= 'write the data'

DELETE SALESPARMSUBTABLE
FROM SALESPARMSUBTABLE
LEFT OUTER JOIN SALESPARMTABLE on
SALESPARMSUBTABLE.PARMID=SALESPARMTABLE.PARMID AND
SALESPARMSUBTABLE.TABLEREFID=SALESPARMTABLE.TABLEREFID
WHERE SALESPARMTABLE.PARMID IS NULL

DELETE SALESPARMUPDATE
FROM SALESPARMUPDATE
LEFT OUTER JOIN SALESPARMTABLE on
SALESPARMUPDATE.PARMID=SALESPARMTABLE.PARMID
WHERE SALESPARMTABLE.PARMID IS NULL

DELETE SALESPARMLINE
FROM SALESPARMLINE
LEFT OUTER JOIN SALESPARMTABLE on
SALESPARMLINE.PARMID=SALESPARMTABLE.PARMID and
SALESPARMLINE.TABLEREFID=SALESPARMTABLE.TABLEREFID
WHERE SALESPARMTABLE.PARMID IS NULL

DELETE SALESPARMSUBLINE
FROM SALESPARMSUBLINE
LEFT OUTER JOIN SALESPARMLINE on
SALESPARMSUBLINE.LINEREFRECID=SALESPARMLINE.RECID
WHERE SALESPARMLINE.RECID IS NULL
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Open salesparmtable table in table browser and delete the records
Steeve 145 on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Hi Mariano,

Thanks for your advice. However, I'm not an AX developer, would it be possible to get a script to delete the records? I can run it in my test environment.

Thanks in advance.

Steeve
Mariano Gracia on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Do it inside Dynamics, if you delete those records in SQL, delete actions won't be triggered, delete SalesParmUpdate as it will delete all the salesparm tables, lines sublines and so on
André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Hi Steeve,

The cleanup job indeed does not delete these lines. You can go to the AOT, select a table and delete the records from the table browser. Or create an SQL script to do it.

I wonder how you got these records in your system.
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

There is no issue deleting these records
Steeve 145 on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Hi Andre,

Thank you for your reply.

In fact I do clean the tables once a month using the sales update history cleanup batch job. However, the job only deletes the executed and erroneous lines. I have some 3,000 old transaction lines which have remained at 'waiting status' in the salesparmtable which I would like to clear. I don't really know why these have not being processed.

The reason I'd like to clean these lines is that I suspect it might be affecting the performance of AX when we create sales orders (For info we are using the eDCCM Orderpad module).

I might be wrong but anyway these transaction lines should not be lying in these tables.

I was wondering if there's a way to clean these old lines with 'waiting' status.

Kind regards,

Steeve
André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

RE: salesparmtable contains old transactions with parmjobstatus still at waiting

Hi Steeve,

These tables gets created when you try to post e.g. a confirmation, packing slip or invoice. When the posting really is not pending anymore, you can clean up the tables. Then there is no impact. When you have scheduled a batch to update some invoices and delete also these records, the invoices will be skipped as there is no record anymore. That is no problem as you can restart updates.

Also check the tables salesparmupdate, salesparmsubtable and salesparmsubline