Microsoft Dynamics AX (Archived)

Using insert recordset in temporary table

(0) Share

Report

Posted on by kartik0603

239

I have created two instance of single temporary table eg: table1 and table2,

First i filled table1 data and then,

I am trying to insert table1 data into table2 using insert_recordset through following code

insert_recordset table2 (field1,field2)

select field1,field2 from table 2;

But it when debugger comes to execution of this statement, AX doesn't respond and have to restart.

What could be the problem...?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Martin Dráb 237,807 Most Valuable Professional on at

Like (0)

Report

I tried to simulate the problem and it all works all right on my system.

Can you please show us full code needed to reproduce the problem? Of course, please cut off everything not related to this issue.

Was this reply helpful? Yes No
kartik0603 239 on at

Like (0)

Report

private void printType()

{

if(contract.parmOriginal())

{

this.insertRecordSet('Original Print')

}

}

private void insertRecordSet(Name _printType)

{

insert_recordset(InvoiceId,PrintType)

select InvoiceId,_printType

from custInvoiceJour;

}

I have to insert the value of parameter in field 'PrintType'

Same way i have different conditions as a parameter.

Was this reply helpful? Yes No
Martin Dráb 237,807 Most Valuable Professional on at

Like (0)

Report

Sorry, it's nothing I could run to see your problem. I also don't see how you're initializing the tables. Furthermore, the content of insertRecordSet() isn't valid X++ code. Please give us real code.

Was this reply helpful? Yes No
Ali Zaidi 4,657 on at

Like (0)

Report

Some times this type of error occurs, You can restart the AOS service and connect the Client again. Hopes this works.

Was this reply helpful? Yes No
Suggested answer

Amir Nazim 5,994 on at

Like (0)

Report

check these few examples

axzaptech.wordpress.com/.../insert_recordset-update_recordset-and-delete_from-single-transaction-command

msdn.microsoft.com/.../aa635694.aspx

Was this reply helpful? Yes No
Volodya on at

Like (0)

Report
Hello,

I have the same issue: DAX 2012 R2 is hanged when tempDB table is used in the insert_recordset bulk operation.

Since there is no Query::insertRecordSet method in R2 i try to mimic dynamic filter by populating the tmpMainAccount tempDB table with selected main accounts (Dialog uses the Main account filter with multilookup)

The code is here, do you know any workarounds?:

StartDate startDate; EndDate endDate; MainAccountNum mainAccountId; DimensionValue location; LedgerParameters ledgerParameters = LedgerParameters::find(); Name dimensionAttributeLocationName = /*ledgerParameters.FRNLocationDim ? ledgerParameters.FRNLocationDim :*/ 'Locations'; DimensionAttribute dimensionAttributeLocation = DimensionAttribute::findByLocalizedName(dimensionAttributeLocationName); FRNTmpDimValue tmpMainAccount; FRNTmpDimValue tmpLocationDim; DimensionFinancialTag dimensionFinancialTag; LedgerJournalTrans ledgerJournalTrans; LedgerJournalTrans ledgerJournalTransOffset; DimensionAttributeValueCombination dimensionAttributeValueCombination; VendTable vendTable; DirPartyTable dirPartyTable; DimensionAttributeValueCombination dimensionAttributeValueCombinationOffset; DimensionAttributeLevelValueView dimensionAttributeLevelValueView; LedgerJournalTable ledgerJournalTable; // Set the userconnection to use on table. // This is required to ensure that createdTransactionId of inserted record is different than default transaction. this.takeOwnershipOfTempTable(vouchersDashboardTrans); contract = this.parmDataContract() as FRNVouchersDashboardDataExportContract; startDate = contract.parmStartDate(); endDate = contract.parmEndDate(); mainAccountId = contract.parmMainAccountId(); location = contract.parmLocationDimensionValue(); this.populateMainAccount(tmpMainAccount, mainAccountId); this.populateDimensionValue(tmpLocationDim, dimensionAttributeLocation, location); this.parmUserConnection().ttsbegin(); insert_recordset vouchersDashboardTrans (AmountCurCredit, DocumentDate, VendorName) select AmountCurCredit, DocumentDate from ledgerJournalTrans where ledgerJournalTrans.AccountType == LedgerJournalACType::Vend && ledgerJournalTrans.AmountCurCredit != 0 && (startDate == dateNull() || ledgerJournalTrans.DocumentDate >= startDate) && (endDate == dateNull() || ledgerJournalTrans.DocumentDate <= endDate) join dimensionAttributeValueCombination where dimensionAttributeValueCombination.RecId == ledgerJournalTrans.LedgerDimension join vendTable where vendTable.AccountNum == dimensionAttributeValueCombination.DisplayValue join Name from dirPartyTable where dirPartyTable.RecId == vendTable.Party join dimensionAttributeValueCombinationOffset where dimensionAttributeValueCombinationOffset.RecId == ledgerJournalTrans.OffsetLedgerDimension exists join tmpMainAccount where tmpMainAccount.Key == dimensionAttributeValueCombinationOffset.MainAccount;

PS: insert_recordset works correctly without the tmpMainAccount exists join.

Was this reply helpful? Yes No
tgudwanski 32 on at

Like (1)

Report
I've experienced a very similar issue while creating data provider class for one of the SSRS reports.

I've needed a number of temporary tables to quickly transform some portions of warehouse transactions data but after report execution, Ax Client hanged.

What helped to solve the issue was to set the same connection for all the tables: temporary and regular which were used in insert_recordset bulk queries.

Below is a little code sample:

if (this.parmUserConnection()) { (...) temporaryTable1.setConnection(this.parmUserConnection()); temporaryTable2.setConnection(this.parmUserConnection()); inventTrans.setConnection(this.parmUserConnection()); inventTransOrigin.setConnection(this.parmUserConnection()); (...) }

The this.parmUserConnection() object was connection object provided by SSRS data provider base class, but may be somehow all of this could be helpful in your scenario.

Please consider also all the previously posted suggestions since I think you might find them helpful for your case.

Was this reply helpful? Yes No
Volodya on at

Like (0)

Report
Thank you it works for me!

I've added the following line of code:

this.takeOwnershipOfTempTable(vouchersDashboardTrans); this.takeOwnershipOfTempTable(tmpMainAccount);

So it seems all tempDB tables should use the same connection.

Was this reply helpful? Yes No
tgudwanski 32 on at

Like (0)

Report

I'm really glad it helped :)

Regards.

Was this reply helpful? Yes No