AX 2012 upgrade - Connect to source database failure

During database upgrade checklist I was failed to connect with source database. I followed the suggested steps from upgrade guide as pasted below but these weren't enough in my case for successful connection.

Configure matching user permissions

Windows integrated security is used to connect to the source database from the target system. If the administrative user who is performing the upgrade on the target system does not have access to the source system database, the source system will reject the database connection. Open Microsoft SQL Server Management Studio on the source system and perform the following steps:

1.     Grant Microsoft Dynamics AX database access to a domain user with administrative privileges on the target Microsoft Dynamics AX 2012 system.

2.     Add the user to the db_owner and public roles.

SOLUTION: 

Make sure you have assigned sysadmin role to the AOS service account from SQL management studio under this path SQLSERVERINSTANCE\Security\Logins\<AOSService Account>. Right click on AOSService account and choose Server Roles from left hand side panel and assign sysadmin role listed on right hand side.

Unit Conversion Check AX2009 Vs AX2012

AX 2009

if(!UnitConvert::canConvert(this.inventTableModuleInvent().UnitId, this.SAB_ProdPickingUOM, this.ItemId))

AX 2012

unitConvFound = UnitOfMeasureConverter::canBeConverted(UnitOfMeasure::findBySymbol(this.inventTableModuleInvent().UnitId).RecId,                                                             UnitOfMeasure::findBySymbol(this.SAB_SalesPickingUOM).RecId,

                                                                   this.Product);

Show methods in AX lookups

This sample code is used to show Customer Name in lookup, In AX 2009 we had name field in CustTable but now its moved to DirPartyTable. We still have name() in CustTable which we can use to display name in lookups.

AX 2009

client static void lookupInterCompanyItemId(FormStringControl   lookupCtrl, DataAreaId _dataAreaId = "")

{

    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable), lookupCtrl);

    Query                   query = new Query();

    QueryBuildDataSource    qbds = query.addDataSource(tablenum(InventTable));

    ;

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemName));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemGroupId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemType));

    if(_dataAreaId)

        qbds.company(_dataAreaId);

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

AX 2012

 client static void lookupInterCompanyItemId(FormStringControl   lookupCtrl, DataAreaId _dataAreaId = "")

{

    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable), lookupCtrl);

    Query                   query = new Query();

    QueryBuildDataSource    qbds = query.addDataSource(tablenum(InventTable));

    ;

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, NameAlias));

    sysTableLookup.addLookupMethod(tableMethodStr(InventTable,itemGroupId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemType));

    if(_dataAreaId)

        qbds.company(_dataAreaId);

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

InventTrans table data model changes in AX 2012 vs AX 2009

The purpose of this post is to give an overview about the changes been made in the Dynamics AX 2012 data model related to inventory transactions.

For more details; how to implement code changes for inventTrans table the best resource is this white paper Implementing InventTrans refactoring for Microsoft Dynamics AX Applications AX2012

Before AX2012, the only table used for recording all the inventory transactions was InventTrans.  In AX2009, a lot of data in InventTrans is either redundant or fields are used to represent a specific type of transaction like sales, purchase, transfer etc.

In AX2012, the base is still the same i.e. every inventory still gets recorded in the InventTrans but the only difference is that the table has been more normalized now. A new table has been added called InventTransOrigin which is actually now holding the relationship between the originating tables (transaction tables) and InventTrans. The InventTransId, TransType and TransRefId have now been removed from the InventTrans and moved to the InventTransOrigin table with names InventTransId, ReferenceCategory and ReferenceId respectively.

Every transaction has now its own InventTransOrigin table. For example, in case of PurchLine the table named is InventTransOriginPurchLine which is actually referring to InventTransOrigin through the InventTransId field. Transaction tables like PurchLine, SalesLine, ProdLine etc. still contains InventTransId field but it not recommended to use/refer them. It is expected to be depreciated in future.