Enabling index hints is deprecated in AX 2012 … almost

Sending index hints to the SQL server from AX has been around for a long time and often it has not done any good since the SQL server is pretty good at picking the best way it self.

So when it ended up as a parameter in the AOS server configuration in AX 2009 and then removed from the configuration in AX 2012 we seemed clear from the trouble it could cause. Microsoft stated that it was deprecated with AX 2012 and no longer available …

So it seemed a bit strange that the SQL server apparently received the information on a server I was working on recently.

While going through about all possible stuff to locate why it was acting like the non-existing index hint flag was enabled, I was going through the registration database to compare it against an AOS we knew was working as expected. And there it was … the registration key called “hint”.

I did a bit of research and I was not the only one struggling with this. As it appears there are these values to choose from :

Empty = Index hints are enabled and LTRIM (yes, it is there too) is disabled.

0 = Both index hints and LTRIM are disabled. This has to be the recommended setting.

1 = The same as empty. Does that make sense? No, not really. Anyways …

2 = Index hints are disabled and LTRIM is enabled.

3 = Both index hints AND LTRIM are enabled

 

And just for refreshing the memory: The location of the registration keys are

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\Dynamics Server\6.0

and in here find the right AOS and configuration.

Advertisements

How to delete a label file in AX 2012

If you end up with an obsolete label file in AX 2012 and want to delete it you cannot just right click and select delete from the popup menu.

To get rid of the label file you can do the following:

  1. Create a new model. In this example named DeleteMe
  2. Move your label file to the DeleteMe model
  3. Use AXUTILs delete function to delete DeleteMe
  4. Restart the AOS

You have successfully removed the label file from your installation.

Manipulating data while ignoring a TTSABORT

OK, the subject of this post will probably be categorised as dangerous and by some as borderline stupid. The content should NOT be considered used in everyday work since it very easily could give you massive inconsistency in data and a broken system. There! I said it and I do not want to be the one saying I told you so afterwards. This blog post is only for information and not a recommendation.

So what is all the fuzz about? Well, sometimes it could be nice allowing some data to be inserted, updated or deleted although a TTSABORT command or an error is thrown. That could be relevant in regards of a log being updated no matter what the result of a job is or the likes.

The mechanism is known from the batch queue that has its status updated no matter how the job finishes.

 

The trick is using the UnitOfWork and UserConnection framework within the TTS-scope. This allows you to create a connection to the database that is not a part of the TTS but is running its own show.

And this is where it gets dangerous/stupid in some scenarios. Imagine a inventory transactions customisation manipulating data in some circumstances within the TTS and some without. The result could be data almost impossible to recover to a consistent state again.

 

In this example we want to update the Tax Group Id on the Customer groups and log the changes to a table no matter what happens.

I have created a table – DEMO_Log – with CustGroup as the only field. We would like this table to receive a new record not depending on success nor failure in the update of the CustGroup table.

Next step is to create a class doing the work. In this case it is called DemoIgnoreTTSAbort and it has a run method like this:

private void run()
{
    info("BEFORE UPDATING");
    this.showInfo();
    
    ttsBegin;
    this.updateCustGroup();
    ttsAbort;

    info("AFTER NORMAL UPDATE");
    this.showInfo();
    
    ttsBegin;
    this.updateCustGroup2();
    ttsAbort;

    info("AFTER ALTERNATIVE UPDATE");
    this.showInfo();
}

It starts by showing the current records in a resume like this

private void showInfo()
{
    CustGroup custGroup;
    DEMO_Log log;

    select count(RecId) from custGroup
        where custGroup.TaxGroupId;

    select count(RecId) from log;

    info(strFmt("Customer groups with Tax group id: %1", custGroup.RecId));
    info(strFmt("Log records: %1", log.RecId));
}

The idea is to give us a count of customer groups with Tax group ids and the count of records in our log table.

Then we – inside a TTS scope – uses the updateCustGroup method to try updating the groups like this:

private void updateCustGroup()
{
    CustGroup custGroup;

    while select forUpdate custGroup
        where ! custGroup.TaxGroupId
    {
        custGroup.TaxGroupId = 'TX';
        custGroup.update();
        this.insertInLog(custGroup.CustGroup);
    }
}

Each CustGroup record with no TaxGroupId content is updated with ‘TX’ and a log is inserted using the method insertInLog that goes like this:

private void insertInLog(CustGroupId _custGroupId)
{
    DEMO_Log log;
 
    Log.CustGroup = _custGroupId;
    log.insert();
}

I think that one is pretty self explaining…

The TTS scope is then ended with an TTSABORT so no records within the scope is updated/inserted.

We then pull the showInfo once more to se if anything has happened. And nothing has. No surprise.

 

The next part is a new TTS scope and a method (updateCustGruop2) which is almost identical to updateCustGroup is used:

private void updateCustGroup2()
{
    CustGroup custGroup;
  
    while select forUpdate custGroup
        where ! custGroup.TaxGroupId
    {
        custGroup.TaxGroupId = 'TX';
        custGroup.update();

        this.insertInLog2(custGroup.CustGroup);
    }
}

 

The only difference between updateCustGroup and updateCustGroup2 is that it calls insertInLog2 instead of insertInLog after updating each record.

This method is the key to all this and it looks like this:

private void insertInLog2(CustGroupId _custGroupId)
{
    DEMO_Log log;
    UnitofWork unitOfWork;
    UserConnection userConnection;

    userConnection = new UserConnection();
    unitOfWork = new UnitofWork();

    log.CustGroup = _custGroupId;
    log.insert();

    unitOfWork.insertonSaveChanges(log);
    unitOfWork.saveChanges(userConnection);
}

Compared to insertInLog it starts by adding to new variables – unitOfWork and userConnection which gives us an extra connection to the database not included in the TTS scope. First is a basic instantiation followed by the insert of the DEMO_Log record like in the insertInLog method. The next statement is where we tell unitOfWork to insert the log record(s) upon the call of the saveChanges method. There is a deleteOnSaveChanges and updateOnSaveChanges if you want something else than inserts.

Finally we call the saveChanges using the above declared userConnection.

Ending the run method with another call of the showInfo reveals that although we abort our TTS scope and the customer groups remain unchanged the log is fully updated:

Message (05:09:54 am)
BEFORE UPDATING
Customer groups with Tax group id: 0
Log records: 0
AFTER NORMAL UPDATE
Customer groups with Tax group id: 0
Log records: 0
AFTER ALTERNATIVE UPDATE
Customer groups with Tax group id: 0
Log records: 7

Once again: Remember that this is only to be used with extreme caution …

Troubleshooting AX/SSRS

The purpose of this document is to list a troubleshoot path to follow when reports cannot be printed from AX 2012.

The contents are not fully covering all options available but points out a toolbox for handling often seen issues.

Troubleshooting

Is it all reports not printing?

Try printing one of the most simple reports available. I usually use the Departments report found in “Organization administration / Reports / Base data / Departments”. If that one prints successfully it is probably not the SSRS causing the problem.

Is SSRS set up correct within AX?

AX has a very flexible setup regarding SSRS. Each AOS has – and must have – its own relation to an SSRS. All AOS’s can point towards the same SSRS but it is also possible to address the reports from each AOS to its own SSRS instance. One AOS can on top this have multiple relations to different SSRS instances. However, only one relation can be default and active.
So how do we make sure all this works?
1. Make sure that all AOS servers have a relation (with default mark set) to an SSRS instance in the SSRS list (Appendix 1 – Locating the SSRS names)
2. Validate the relations using the “Validate settings” button.
3. Verify that the specified URLs are valid.

Is SSRS set up correct regarding connection to AX?

With AX pointing towards the right SSRS instance we need to make sure that the SSRS instance points towards a valid AOS. With only one SSRS instance on the server it – by default – looks up the default client configuration on the server to access AX. Having multiple SSRS instances on one server requires each instance to have an configuration file with the name Microsoft.Dynamics.AX.ReportConfiguration.axc. Make sure that this configuration file is valid. For instance by opening a client with it if possible. Also make sure that the WCF part of the file is fully updated. To rebuild the WCF part follow the description in the section Rebuild the WCF part of the AX configuration for the SSRS.
Another way to test that multiple instances points towards the correct instances is to create eg. a department called “Development” in a development environment and one called “Test” in the test environment and then verify that these are shown when printing from the relevant AX environments or the SSRS report manger site (see description of how to print from outside the AX client).

Does it print from outside the AX client?

SSRS offers a web frontend allowing us to test – at least some – reports from outside AX pretty easily. To do so you need to find the URL address to the report manager site. This is listed in the Report server list found within AX. To find this list please read the description in Appendix 1 – locating the SSRS names.
From here navigate to the URL specified in the field Report Manager URL.
If this does not open up succesfully go directly to restarting the SSRS service.
aIf it opens up successfully click on the DynamicsAX folder and use the Search field in the upper right corner to locate the report design HcmDepartmentReport.DepartmentDesign

Printing reports from the SSRS Report Manager

Printing reports from the SSRS Report Manager

Click on the report to show it. If a report is shown the report server and the connection works.
If not printed successfully you might be presented with an error description pointing you in a useful direction.
Rebuild the WCF part of the AX configuration for the SSRS
The WCF part of the AX configuration describes the service ports available. It can be usefull to rebuild these in some circumstances eventhough you do not think they have been altered. To update this open the Microsoft Dynamics AX Client Configuration on the server running the SSRS. Click Manage and “Open a configuration file…”. Navigate to C:\Program Files\Microsoft SQL Server\MSRS11.<Name of the relevant instance>\Reporting Services\ReportServer\bin and select the file Microsoft.Dynamics.AX.ReportConfiguration.axc.

Rebuild the WCF part of the configuration file

Rebuild the WCF part of the configuration file

On the Connection tab click the Refresh Configuration button and wait …

Waiting for the WCF to be rebuild.

Waiting for the WCF to be rebuild.

When completed click Apply and “Manage / Save configuration file”.
After saving the file restart the SSRS-service.

Appendix 1

Locating the SSRS names

The name of the server can be found from within AX by looking up the reporting server(s) listed in “System administration / Setup / Business intelligence / Report servers”.
Notice, that the list can contain multiple SSRS instances and not all of them are related to the environment you are trying to fix.

Restart the SSRS-service

This is a fairly quick service when it comes to restarting so you will not be troubling users for that many seconds.
Log on to the server running SSRS. From here locate the service “SQL Server Reporting Services (<Name of the relevant instance>)” and restart it.

Change installation folder for the AOS server

Working for a company that handles development of multiple modules/add-ons to AX we end up having a lot of AOS-services on one server. We do that to separate development and release environments and keeping modules isolated from each other.

The problem that we sometimes end up with is that the C-drive reaches max and we need to install some of the AOS services on a different drive.

Going through the normal setup routine does not offer any options of specifying the installation folder. So we need to do two modifications before it works.

First step is to open the regedit.exe and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Dynamics\6.0\Setup\Components\InstallDir and change the value to where you would like the AOS to be installed.

Second step is to make sure that the Server Setup files is present in the new directory. To do this simply copy the folder C:\Program Files\Microsoft Dynamics AX\60\SetupSupport to the new drive so you have a D:\Program Files\Microsoft Dynamics AX\60\SetupSupport or wherever you want the AOS servers installed.

Run the AX setup and your AOS is now installed on your new drive.

Roaming AUC files – new feature in R3

The quest for increasing performance in AX appears never-ending. With AX 2012 R3 Microsoft has added a new tool allowing us to make the AUC files global/shared.

Basic AUC file info:

AUC files are cache files made by the AX client to improve load time. Forms we have already loaded once could be loaded again from the cache without having to make a trip to the AOS for the bits and bytes. All stored in c:\users\<username>\AppData\local and then by nature user specific.

So the main challenge so far has been that user A opens the sales order form the first time, waits, caches the element and can open the form faster the second time and the same story for user B, C and all the others.

In AX 2012 R3 a new feature have been added to the client configuration with the title “Use roaming user profile to store client-side cache”. That basically says it all. We can now specify a global directory for the cache files allowing users to share the cache so when user A has opened the form user B can utilise the cache.

Still no fun for user A, but user B is in luck …

AOS cannot start after extending string length on EDT

All though AX 2012 is constantly moving the boundaries for what we can do we ended up hitting the roof on field/record sizes the other day.

After heavily increasing the string length on an EDT and a crashed synchronisation the AOS was unable to start up again. The error message received in the Event log was this:

Object Server 01: Unexpected situation
More Information: Total field length cannot exceed 64K. Please re-factor your table <tablename>

The obvious solution is to decrease the length of the EDT to get below the 64K limit.

But – to cut this short –  we now have an AOS that cannot start due to an error we need the AOS running to fix. Well, we knew that we had a backup taken a couple of hours ago giving us a loss of changes that we could deal with if it were. However, it was worth giving it a shot trying to fix this.

The solution was quite simple. We found a similar versioned environment and did the following:

  1. Create a new model
  2. Make a small customisation to the EDT giving us the problems putting it in the new model
  3. Export the model
  4. Import the model in the model database of the AOS that will not start
  5. Start the AOS
  6. Skip the models changed dialog and do a full synchronisation from the AOT

Voila! We are back on track with no loss except for the time spent doing the above thanks to the model concept and AXUtil.