Skip to main content

Notifications

Microsoft Dynamics AX forum
Suggested answer

Using insert recordset in temporary table

Posted on by 237

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...? 

  • tgudwanski Profile Picture
    tgudwanski 32 on at
    RE: Using insert recordset in temporary table

    I'm really glad it helped :)

    Regards.

  • Volodya Profile Picture
    Volodya on at
    RE: Using insert recordset in temporary table

    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.

  • tgudwanski Profile Picture
    tgudwanski 32 on at
    RE: Using insert recordset in temporary table

    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.

  • Volodya Profile Picture
    Volodya on at
    RE: Using insert recordset in temporary table

    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.

  • Suggested answer
    Amir Nazim Profile Picture
    Amir Nazim 5,994 on at
  • Ali Zaidi Profile Picture
    Ali Zaidi 4,657 on at
    RE: Using insert recordset in temporary table

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

  • Martin Dráb Profile Picture
    Martin Dráb 227,996 Super User 2024 Season 2 on at
    RE: Using insert recordset in temporary table

    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.

  • kartik0603 Profile Picture
    kartik0603 237 on at
    RE: Using insert recordset in temporary table

    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.

  • Martin Dráb Profile Picture
    Martin Dráb 227,996 Super User 2024 Season 2 on at
    RE: Using insert recordset in temporary table

    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.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,186 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,996 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans