Getting Data from TEMP table in AX 2012R2

(0) Share

Report

Posted on by Lionel07

495

Hello Everyone,

I have an urgent request to extract some data from a temp table... I need to use a query to get some info from these two(2) tables:

CustTable table
CustAgingReportTmp table
CustTable is a real table but the CustAgingReportTmp is a temporary table... how can I create a query to get data from that table.

Please and thanks.

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report

What kind of query are you interested in? Where and how do you plan to consume the data?

Temp table has data only if you populate it, and the data disappears once the table pointer is no longer in scope. So you need to have the population and data retrieval in the same process.

Was this reply helpful? Yes No
Lionel07 495 on at

Like (0)

Report

Thanks Nikolaos, so what I want to do is extract some data from the AX 2012r2 database. Some of the data am interested in would be coming from the CustAgingReportTmp table. This is data for auditing purposes. I thought that when i run the report Customer Aging and keep the report open, I would be able to see the data in CustAgingReportTmp table.. Please help if you can I urgently need it.

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,671 Moderator on at

Like (0)

Report

Hi Lionel07,

given this is one time request for Auditing purposes, you can change the table to permanent. That would retain the data that you can further extract. Once extracted, revert the change. See if you can leverage this approach.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,166 Moderator on at

Like (1)

Report

I think the table is discarded when the report is printed.

Anyway, during the existence of the table you will find it in the tempdb database. But I don't think you should pursue this path longer. Instead you need to figure out some other way to get this data.

For example, you can copy this table and make this copied version a non-temporary table.

Then, when data is inserted in the temp table, populate the same record in your normal table.

When you don't need the data anymore, delete it from your normal table.

Also this is a "quick and dirty" solution but since you requested for urgent help, perhaps you could try that.

Was this reply helpful? Yes No
Lionel07 495 on at

Like (0)

Report

This cannot be done. i went on the table properties and change the TableType to Regular but when i run the report it gave me a message saying the table has to be TempDB

Was this reply helpful? Yes No
Lionel07 495 on at

Like (0)

Report

CAn you please advise how i do this.... It didnt work form the properties of the table

Was this reply helpful? Yes No
Satish Panwar 14,671 Moderator on at

Like (0)

Report

Hi Lionel07,

Table properties the only way i know. Give the solution that Nikolaos proposed a try.

Was this reply helpful? Yes No
Suggested answer

startax 1,845 on at

Like (0)

Report

Hi Lionel ,

You cant change the table property because of DP class. DP class is extended with standard class that allow only Temp table to populate data in report.

Nikolaos approach is the good option for your requirement . But if you need this on regular practice then you have to find some other option.

Was this reply helpful? Yes No
Lionel07 495 on at

Like (0)

Report

Thank you, everyone for your suggestions and help.

Was this reply helpful? Yes No