TempDB insert issue in Dynamics AX 2012 R3

I recently had an experience of an unknown issue while creating estimate for a Project in Project management and accounting module. The error displayed was

Initially, I was expecting it to be an issue with TempDB. I was in an impression that tempDB table is not refreshed or it can be linked with known issues as mentioned here

http://blogs.msdn.com/b/axinthefield/archive/2014/02/27/errors-in-aos-event-log-after-sql-server-cluster-failover-or-alwayson-availability-groups-failover.aspx

However, When I started to debug the code There is a statement which doing bulk inserts from ProjItemTrans to tmpProjControlActual table. While copying text field from ProjItemTrans to tmpProjControlActual, it throws error if you have specified more than 60 characters.

Table	Field	Length
ProjItemTrans	Txt	1000 (EDT: ItemFreeTxt)
tmpProjControlActual	Txt	60 (EDT: TransTxt)

That is why if you try to copy more than 60 characters into a 60 character field, AX will throw error.

I found it is a bug in AX 2012 R3 because txt field should have same length.

In order to fix the issue, I had to manually update tmpProjControlActual.txt field to 1000 characters.

Remember to restart SQL service and then AOS service as TempDB won’t refresh until you restart SQL service.

Happy DAXing

Map SQL SPID with AX session ID in Dynamics AX 2012

In Microsoft Dynamics AX 2012 the SPID column is no longer available on the “Online Users” form. However, Information about the user sessions can be directly included in the SQL connection context with a small performance overhead (That is why it is not turned on by default).

To include user session information in the connection context perform the following steps:

1.     Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Dynamics Server\6.0\01\Original (installed configuration). Key named "Original" (installed configuration), is the key name for the current server configuration (if original installed configuration is active configuration).

2.     Create a string registry value called ‘connectioncontext’ and set the value to 1.

3.     Restart the AOS.

Now open SQL Server Management Studio and execute following query

select cast(context_info as varchar(128)) as CI,* from sys.dm_exec_sessions where program_name like '%Dynamics%'

For sample example, it will look like

Second item in the CI column is session ID and second column is your SPID. Open Online user form to map these two values and you'll be able to map. Hope this is helpful. Remember to monitor performance impact because of this registry edit. Enable it only if you are having blocking threads and not able to identify the real cause.

Want to know more about "CLRObject object not initialised"?

I recently had an experience and would like to share. When I was trying to upload product image to a product in Dynamics AX 2012. It returned with following error

Message "CLRObject object not initialised." was really misleading. I was debugging and as soon as it went to read WINAPI -> System.IO.CopyFile(*,*,*) it was always returning error as mentioned above.

In order to see what the actual thrown message is, I had to write custom code in copyFile method of WinAPI class (highlighted code is the code to display actual message)

So from above modification, I was able to see the more descriptive error which was

Now the actual message was related to un authorized access to the folder where it saves temp image files (selected on parameters form of document management). I went to that folder and assigned write permissions to domain users. There can be many reasons for this CLRObject error. However, making this change in code for debugging will let you quickly understand the actual pain.

Hope, this is helpful for you guys.

Microsoft Dynamics AX 2012 R3 Solution Demo Package V1.0 released

Microsoft has released latest/new version of the Microsoft Dynamics AX 2012 R3 demo machine. Download is available on both partner and customer source.

Customersource:https://mbs.microsoft.com/customersource/northamerica/AX/downloads/service-packs/AX2012DemoToolsMaterials

Partnersource: https://mbs.microsoft.com/partnersource/northamerica/sales-marketing/demo-tools/virtual-machines/AX2012DemoToolsMaterials

Simple and easy to digest demo videos from Microsoft sellers. Please refer to the section: Demo Videos on the above link for Microsoft Dynamics AX 2012 R3.

Source: https://mbs.microsoft.com/partnersource/northamerica/sales-marketing/demo-tools/virtual-machines/AX2012DemoToolsMaterials

Happy DAXing!!

Long running SSRS reports in Dynamics AX 2012

Quite often we come across a problem that our developed report works fine on development and test environments. However, when deployed to LIVE, customers are having issues that report is timed out!! The reason is that our development environment do not have huge data like our LIVE system so it timed out after 10 minutes. Default timeout of AOS and SSAS interaction is 10 minutes so if your report is taking more time to fill up datasource/temptable then you will have time out error after 10 minutes.

This issue is more common in reports like trial balance and customer aging reports which takes longer to fill report temp tables.

There are two possible solutions

 - Increase the default time out setting for all

 - To perform following to mitigate time out error

By performing following steps you will be able to increase the time out for reports that takes longer than 10 minutes. Recommendation is to always execute these reports in batch as it will keep your AX client busy and you won't be able to perform any other task during that processing.

1.      Extend your RDP class from SSRSReportDataProviderPreProcess instead of SSRSReportDataProviderBase

2.      Update your table type (from table properties) from InMemory to TempDB

3.      Perform full compile of IL

4.      Restart the AOS

5.      Introduce controller class to run the report (if not already there)

6.      Point your menu item from actual report to controller class

You will have following differences after this modification

1.      Will not have "A connection attempt failed because the connected party did not properly respond after a period of time" error.

2.      Will not see the report window immediately. AX will fill up the temp table and then display report window.

3.      Still should put such jobs in batch as it will keep your AX in the process and won't allow to perform any other operation. 

Pass multiple records from one form to another

I came across a requirement to pass multiple records from one form to another. It might would be a very common practice now. However, I thought to blog it for if anyone still not cleat with this part.

Instead of passing datasource as an argument in the property, Override clicked() method of button and prepare your args to pass as shown. I am taking an example of SalesTable to pass to another form

void clicked()

{

    int         recordsCount;

    SalesTable  salesTable;

    container   con;

    Args        args;

    str         multiSelectString;

    args = new Args();

    // gets the total records selected

    recordsCount = salesTable_ds.recordsMarked().lastIndex();

    salesTable= salesTable_ds.getFirst(1);

    while (salesTable)

    {

        // storing recid of selected record in container

        con = conIns(con,1, salesTable.RecId);

        // converting container to string with comma separated

        multiSelectString = con2Str(con,’,’);

        salesTable= SampleTable_ds.getNext(); // moves to next record

    }

   

    // passing string

    args.parm(multiSelectString);

    // calling menu item

    new MenuFunction(menuitemDisplayStr(NewFormMenuItem), MenuItemType::Display).run(args);

}

Now in the above method we have already prepared our args in a container and have passed that to a new form of menu item "NewFormMenuItem"

In order to retrieve passed arguments in the recipient from. Override the init() method of new form as shown

public void init()

{

    container   con;

    int         i;

    str         multipleRecords;

   

    super();

   

    // to get string value from caller

    multipleRecords = element.args().parm();

    // string to container

    con = str2con(multipleRecords,”,”);

    // for sorting

    for(i = 1;i<= conLen(con) ;i++)

    {

        salesTable_ds.query().dataSourceTable(Tablenum(SalesTable)).addRange(fieldNum(SalesTable,RecId)).value(SysQuery::value(conPeek(con,i)));

    }

} 

If you have some more improved versions of this task please share.

Happy DAXing!

Set Value On a Control in List Page through ListPage Interaction class (Dynamics AX 2012)

I recently had a requirement to display sum of total volume (a display method) of all the selected records on a List page form. There were two major development tasks in this requirement

1. To loop through all records that user has selected on List Page.
2. To set value on a custom control of List Page after calculation.

You can easily loop through list of selected records with in interaction class by using MultiSelectionHelper class. However, I found it a bit tricky in setting up the control value of List page as previously I only used this class to enable/disable or make visible true false of controls or buttons on list page.

Here is the code for your reference if you ever need to do the same.
For sure there will be many other more effective ways of doing this. If you ever had to do and know the best way please share with me as well.

protected void setSumOfTotalVolume()

{

    ReqPO               reqPO;

    Volume              totalVolume;

    Common              externalRecord;

    FormDataSource      frmDs;

    FormRun             formRun;

    FormRealControl     frmCtrl;

    MultiSelectionHelper helper = MultiSelectionHelper::construct();

    helper.parmDatasource(this.listPage().activeRecord('ReqPO').dataSource());

    reqPO = helper.getFirst();

    while (reqPO.RecId != 0)

    {

        totalVolume += reqPO.totalVolume();

        reqPO = helper.getNext();

    }

    //Assign value to control on list page

    externalRecord = this.listPage().activeRecord('ReqPO');

    if(externalRecord.isFormDataSource())

    {

        frmDs   = externalRecord.dataSource();

        formRun = frmDs.formRun();

        if(formRun)

        {

            frmCtrl = formRun.design().controlName(formControlStr(ReqTransPOListPage,TotalVolumeField));

            if(frmCtrl)

            {

                frmCtrl.realValue(totalVolume);

            }

        }

    }

}

You should also call this method from interactionclass.selectionchanged() method to update value on every record selection.

Hope it will be help full for if you have a similar requirement in future.

Happy DAXing !!