Category Archives: Uncategorized

[AX2012] Fetching a Label value from SQL

Coming from my previous blog post Get list of security roles with name , I extracted the label via SQL. I had to break up the Label ID into the Module and then the number.
This allowed me to then query the table ModelElementLabel to get the label value

Following code can get the Label via SQL
NOTE: you need to query the Model database for this

DECLARE @LabelStr as nvarchar(50)
Set @LabelStr = N'@SYS12345'

select * from ModelElementLabel L
    where L.Module = SUBSTRING(@LabelStr, 2, 3)
          and L.LabelId = SUBSTRING(@LabelStr, 5, 7)
        --and L.Language = 'en_us' -- Uncomment this line to filter by language

This way you wont need to search a label value from within Ax

AX2012 – Delete Ax Transactions

So there are apparently times when you would want to delete the transactions from the Ax 2012 installation, keeping all the parameters and configurations intact.

Ax has always had a class called SysDatabaseTransDelete which help in deleting the transaction tables. There are however rules on how and why it deletes them which can be followed by viewing that class.

In my Ax2012 cu7 environment I created a job which implements the same concept like the class, but outputs the result to a CSV file for you to analyze what will be deleted and what wont along with the record count. The record count is however company specific, so i would encourage you to dig straight into SQL to find the tables sizes and merge the results from there.

The Job in Ax 2012

Download the XPO and have a go at it.

It creates a File (hardcoded) in CSV format with the following header:

Table Group,Company Specific,Table name,SQL Table Name,Will be deleted,Row Count

Open the file in Excel, filter, sort, have fun.

What to learn from this

Table Group should be specified for every table that you create in Ax. If you leave it as miscellaneous, then Ax will never use it. This property is also used in analytics so its highly recommended to set this up.

Global Tables are not deleted by this unless handled specifically.

AX 2009 – AIF Generation resets Web.config – Resolution

I have a scenario where i have multiple AIF Services on one machine connecting to different AOS’s

Which means every time I generate the service, the web.config file resets itself. Which means the value of BUSINESS_CONNECTOR_CONFIGURATION is blank.

That makes me Unhappy.

So that made me go digging into the service generation classes, so that I can edit the code that will make me happy. Turns out the first time the web.config file is created (I still need to work out on where the first one is created from), it saves it to the folder : C:\Program Files\Microsoft Dynamics AX\50\Application\Appl\AX_MYAPPLICATION_2009\ServiceGeneration

So the next time you generate the service, it uses this file as a master template. YES… master template. Check the code at: Classes\AifServiceConfigGenerator\getConfigFile

So when you know your appsettings, edit this web.config. Next time you generate the service, the BUSINESS_CONNECTOR_CONFIGURATION stays what it is.

This means that if you have multiple AIF sites for the same AOS, then you probably should use a UNC path to the appsettings, so that you dont have to change it on some other machines.