Linking AX Sessions with SQL SPIDs

It is useful to be able to link the DAX session numbers with the SQL SPID, especially if you want to diagnose who is causing locks, or long running queries. This is one method to get the information.

Before you can link the two pieces of information you need to enable an extra mode on the AOS server(s). On each AOS you want to review the DAX sessions against the SQL SPID you need to complete the following setup.

Continue reading

Updating the SID

While not a common activity, there are times you might moved your DAX database from one domain to another. This might be because of testing or because development is offsite. If this occurs you need to change the admin account in DAX to point to the new domain. Below are the steps needed to do this:

Continue reading

Strings and single quotes

When programming in DAX you can use both double quotes ” and single quotes ‘ to contain strings. You must use double quotes when using labels, but aside from that, functionally they work the same.

One consideration is the best practices. Personally I use compile level 4, which will flag up unused variables and lots of useful information to keep your code clean, but it also flags up strings that are not labels. This is often a problem with data migration scripts as you will often use strings, but wont want to create DAX labels for them. You also don’t want the best practice engine from flagging them up, so the best course of action is to use single quotes to contain the strings as this will not cause a best practice warning.

If when using single quotes and you want to include a single quote within the string, you need to ensure you use \’ where you want the single quote, as this will be interrupted as a single quote within a string.

Data importing and the modifiedfield control

When importing data into a table the programmer should be aware of any modifiedfield controls that are present on that table. These controls might be used to update other fields, or even other tables, and rather than recreating the code in your import scripts, it is much simpler to use the system code to perform these functions for you.

After your field is updated you can call:


Using this method will mean if over time, between the development of the import code, and the actual use date, if the system code is changed, your code will still function in alignment with how the system now works.

Linking Purchase order and sales orders – intercompany

Following on from my recent blog Linking purchase orders with sales order using x++, you might also want to link a purchase order with a sales order that is intercompany.

If you know the sales order number of the end sales order you need to link with, then you can use the sales table field, InterCompanyOriginalSalesId, to find the sales order number from the end sales order of the intercompany order.

Batch code and CIL

When developing code that will be running in CIL mode, for example Batch jobs, it is important that after changes are made to code that the system is CIL compiled, incremental or full.

If you do not CIL compile then the old code will be running, not the new code, as only compiled CIL code is processed.

ResultSet getstring limits

If you are using direct SQL to access data in another SQL system, you will typically be using the ResultSet data handler. To extract of the different fields contained in the ResultSet uses the appropriate get commands depending on the variable type. If you are trying to get a string you might encounter a problem were the limit of the data returned will be 999 characters.

Beware of the getstring limit of 999 characters, and if your field might be longer than this, you will need to handle this in the SQL statement and merge the data together after.