ODBC connection properties – setOther()

I’m working on an implementation that interfaces Dynamics AX to a number of external systems using an Enterprise Service Bus.

InterfaceOverview_Generic

The Enterprise Service Bus uses tables in a SQL Server database so we’re using an ODBC driver to move the data from Dynamics AX. The interfaces are coded in X++.

One of the first challenges we encountered is the requirement for record updates – first we do a select on the target table; if the returned resultset is null we can perform an insert otherwise we must perform an update. But the standard ODBC connection objects don’t support this by default. We need to use a feature of the native SQL Server ODBC driver called “Multiple Active Result Sets” (MARS), which is explained here. We do this in X++ like this:

protected void initialiseForTSQL()
{
    LoginProperty   login;
 
    login = new LoginProperty();
    login.setDSN(GCGInterfaceParameter::find().DSN);
    login.setOther('MARS_Connection=Yes');
 
    connection = new OdbcConnection(login);
}

The application was initially AX 2012 R2 with kernel build 6503 and everything worked well.

Last week, we upgraded the kernel to 9152 and the interfaces broke with this error:

Data source name not found and no default driver specified

We logged a support call with Microsoft and very promptly the engineer told us:

The problem occurs whenever we’re trying to initialise an ODBC connection from X++ and calling the setOther() method on the LoginProperty. The kernel change that introduced this was shipped with KB 2964481. The kernel code parses the parameters we specify in the connection string but then, if “other” parameters are specified using “setOther()”, we reset the connection string and use these custom parameters.

So the call to setOther() which sets the MARS value removes the DSN we had specified and causes a logon error. There is no hotfix publicly available for KB2964481 but a solution is straightforward to code in X++. We specify all our connection values in the call to setOther() and our code now looks like this:

protected void initialiseForTSQL()
{
    LoginProperty   login;
    str             connectionString;
 
    login = new LoginProperty();
 
    connectionString = strFmt('DSN=%1;MARS=Yes', GCGInterfaceParameter::find().DSN);
    login.setOther(connectionString);
 
    connection = new OdbcConnection(login);
}

Calling Number sequences with scopes

Generating number sequences in AX 2012 is straight forward but sometimes might be a bit tricky. In AX 2012, number sequences can be defined in different scopes which in turn define where a certain number sequence is generated. There are several scopes defined: Company, Legal entity, Operating unit, Shared (between all companies)…etc.

Issue

In my example below, the customer wanted to identify the store in which invoices have been created from. So, they requested a different number sequence pattern for the invoices generated from each store they own.

Solution

The solution was to create a new field in the store to reference a number sequence. Each store will have a number sequence which is specific to it.

1- In the following exercise, I’ve created a new number sequence with the following properties:

1

From the screen shot, the scope of the number sequence is set to ‘Shared’. Changing the scope of the number sequence might change how it can be called to generate numbers in custom code (which will be shown later in this example).

2- I referenced the created number sequence above in the new field “Italian number sequence invoice” as shown below:

2

This field (Italian invoice number sequence) is shown in table RetailStoreTable below and will hold a value for NumberSequenceTable.RecId

3

 

3- Calling this number sequence might be slightly tricky, as the code written will not follow the conventional pattern of creating number sequences. E.g. The reference is NOT added to the LoadModule method in NumberSeqModuleRetail class shown below. Instead it’s added as a field to each store as shown above.

4

 

This mean that the following code to call the reference won’t be valid:

NumberSeq::newGetNum(NumberSeqReference::findReference(extendedTypeNum(DTGItalianInvoiceNumSeq)));

Instead, the following code is built to call this number sequence. For simplicity, I created a method that takes the store Id and then generates a number sequence:

public server static Num DTGCreateStoreNumberSequence(RetailStoreId _storeId)
{
    NumberSeq           numSeq;
    RetailStoreTable    retailStoreTable;
    ;
 
    //Get the retail store table
    retailStoreTable = RetailStoreTable::find(_storeId);
 
    if(retailStoreTable.RecId != 0 && retailStoreTable.DTGItalianInvoiceNumSeq != 0)
    {
        numSeq = NumberSeq::newGetNumFromCode(NumberSequenceTable::find(retailStoreTable.DTGItalianInvoiceNumSeq).NumberSequence);
        return numSeq.num();
    }
    else
    {
        return "";
    }
}

In the code, the method that creates the number sequence instance is:

numSeq = NumberSeq::newGetNumFromCode(<NumberSequenceCode>);

This method as it now will throw the following error:

5

 

This is because the scope that the system is using to search for the number sequence is the current company (the default scope) whilst the number sequence I created is in the shared scope. To fix this, the following code will be changed from:

numSeq = NumberSeq::newGetNumFromCode(NumberSequenceTable::find(retailStoreTable.DTGItalianInvoiceNumSeq).NumberSequence);

to:

numSeq = NumberSeq::newGetNumFromCode(NumberSequenceTable::find(retailStoreTable.DTGItalianInvoiceNumSeq).NumberSequence,
                                              NumberSeqScopeFactory::createGlobalScope());

The code: NumberSeqScopeFactory::createGlobalScope() is meant to change the scope to shared.

AX 2012 R2 Cannot create a record in Address format lines when synchronising database

When synchronising a database right at the end of the process you will receive the following message if there are two address lines set up with the same format.

AX 2012 R2  Cannot create a record in Address format lines

The AX client allows you to configure this without any error message. During the synchronisation process an additional process is run at the end of the synchronisation that causes the error message to be displayed.

Offending line in this a case:

Navigation:Company/Organization administration/Area page

image imageimage

Once I removed the duplicate address format setup the synchronisation completed fine.