Error Message When Synchronize Microsoft Dynamics AX 2009 Data Dictionary

After restoring the Database from the Production Environment on to the Test Environment I synchronized the data dictionary and than I got the sync error.

I investigate and found out that the issue was in the CRTCards table. It has the unique index for CardNumber Field and the table contains the duplicates records for card number.

I just go to the SQL Server Management Studio and write the below query and run it to check for the duplicate records and delete them.

WITH CTE AS(
 SELECT CARDNUMBER,
 RN = ROW_NUMBER()OVER(PARTITION BY CARDNUMBER ORDER BY CARDNUMBER)
 FROM CRTCARDS
 )
 DELETE FROM CTE WHERE RN > 1

Below are the links for more detail analysis on this:

https://support.microsoft.com/de-ch/kb/937765

http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server

Thanks

Muhammad Zahid.

Getting Financial Dimension Values through X++

I was working on the  SSRS reports there was the scenario of showing the individual dimensions values i.e Business Unit, Cost Center, Department, LineOfBusiness, LineOfRevenue.

I have the generalJournalAccountEntry table in my query which cantains the LedgerAccount that contains the financial dimension values in combine i,e 602100-001-026-014-. This account contains the values of main account i.e 602100, business unit i.e  011, department i.e 026,  costcentre i.e 014.

My scenario  was I have to break this accounts into individual dimensions at run time.

After some research I got the code to get the financial dimension values by ledgerDimension which is already present in GeneralJournalAccountEntry table.

Below is the code snippet to achieve this:

private void addDimensionSegments(MzkLedgerTransactionListStagingTmp _ProcessingStagingTable, GeneralJournalAccountEntry _generalJournalAccountEntry)
{
    // DimensionAttributeValueCombination stores the combinations of dimension values
    // Any tables that uses dimension  combinations for main account and dimensions
    // Has a reference to this table’s recid
    DimensionAttributeValueCombination  dimAttrValueComb;
    //GeneralJournalAccountEntry is one such tables that refrences        DimensionAttributeValueCombination
    //GeneralJournalAccountEntry          gjAccEntry;//TODO commment zahid
    // Class Dimension storage is used to store and manipulate the values of combination
    DimensionStorage        dimensionStorage;
    // Class DimensionStorageSegment will get specfic segments based on hierarchies
    DimensionStorageSegment segment;
    int                     segmentCount, segmentIndex;
    int                     hierarchyCount, hierarchyIndex;
    str                     segmentName, segmentDescription;
    SysDim                  segmentValue;
    ;

    setPrefix("Dimension values fetching");
    //Fetch the Value combination record
    dimAttrValueComb = DimensionAttributeValueCombination::find(_generalJournalAccountEntry.LedgerDimension);
    setPrefix("Breakup for " + dimAttrValueComb.DisplayValue);

    // Get dimension storage
    dimensionStorage = DimensionStorage::findById(_generalJournalAccountEntry.LedgerDimension);
    if (dimensionStorage == null)
    {
        throw error("@SYS83964");
    }

    // Get hierarchy count
    hierarchyCount = dimensionStorage.hierarchyCount();
    //Loop through hierarchies to get individual segments
    for(hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)
    {
        setPrefix(strFmt("Hierarchy: %1", DimensionHierarchy::find(dimensionStorage.getHierarchyId(hierarchyIndex)).Name));
        //Get segment count for hierarchy
        segmentCount = dimensionStorage.segmentCountForHierarchy(hierarchyIndex);

        //Loop through segments and display required values
        for (segmentIndex = 1; segmentIndex <= segmentCount; segmentIndex++)
        {
            // Get segment
            segment = dimensionStorage.getSegmentForHierarchy(hierarchyIndex, segmentIndex);

            // Get the segment information
            if (segment.parmDimensionAttributeValueId() != 0)
            {
                // Get segment name
                segmentName = DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId()).DimensionAttribute).Name;
                //Get segment value (id of the dimension)
                segmentValue        = segment.parmDisplayValue();
                //Get segment value name (Description for dimension)
                segmentDescription  = segment.getName();
                ///info(strFmt("%1: %2, %3", segmentName, segmentValue, segmentDescription));
                if(segmentName == 'BusinessUnit')
                {
                    _ProcessingStagingTable.BusinessUnit = segmentValue;
                }
                else if(segmentName == 'LineOfBusiness')
                {
                    _ProcessingStagingTable.LineOfBusiness = segmentValue;
                }
                else if(segmentName == 'LineOfRevenue')
                {
                    _ProcessingStagingTable.LineOfRevenue = segmentValue;
                }
                else if(segmentName == 'Location')
                {
                    _ProcessingStagingTable.Location = segmentValue;
                }
                else if(segmentName == 'Department')
                {
                    _ProcessingStagingTable.Department = segmentValue;
                }
            }
        }
    }
}

Thats all from my side.

More detail to this can be found on the below link:

https://sumitsaxfactor.wordpress.com/2011/12/16/getting-individual-dimension-combination-valuesdimension-storage-class-ax-2012/

Microsoft Dynamics AX 2012 Leading Zero Reading From Excel Sheet

I was importing the financial dimensions values i,e Business Unit , Cost Centre and Department.

I got stuck while importing the value 02 and after reading this value from excel sheet it was considered as 2.

after searching a lot to fix this issue I found the following links that helps me to resolve this issue.

http://dynamicsuser.net/forums/p/72274/384995.aspx

http://dynamicsuser.net/forums/t/53171.aspx

Thanks

Muhammad Zahid.

Deleting Legal Entity and its Transactions

Below is the the link that contains the info on how to delete the Legal Entity and its Transactions.

http://daxture.blogspot.com/2014/09/delete-legal-entities-in-ax-2012.html


Thanks

Muhammad Zahid. 

Microsoft Dynamics AX 2012 DataDictionary Synchronization errors

There was a working environment of Microsoft Dynamics AX 2012 with data.

I just changed its database file without changing the model file.The database file was without data.

After attaching the database i try to synchronized the data dictionary.

After synchronization I got the following error message on around 18 tables:

Below is the screen shot of this error that i was getting

I also compared the ids of the objects on which I was getting the errors in the sqldictionary table and the AX environment but the ids were same.

After getting some help from the below links I deleted the table on which I was getting the sync errors in the SQL Server Management Studio and than sync that table on Micosoft Dynamics.

After doing the above steps on each of the table that's generates the errors my issue was resolved.

Below are the links for more info on this.

http://blogs.msdn.com/b/axsupport/archive/2012/09/19/troubleshooting-aot-synchronize-errors.aspx

https://community.dynamics.com/ax/f/33/t/143042


Thanks

Muhammad Zahid.

Sql Query to Copy Data from one table of Database to Another table of different database

To copy large amount of data from one table of one Database to another table of different Database.We can use the below query.

note:To copy the data one thing keep in mind that the table definition in both the databases should be the same.

INSERT INTO MicrosoftDynamicsAX..userinfo 

SELECT * FROM MicrosoftDynamicsAX_Deleted..USERINFO where USERINFO.ID = 'admin'

The above code will copy the data from "userinfo" table of database "MicrosoftDynamicsAX_Deleted" to the "userinfo" table of database named "MicrosoftDynamicsAX"

Thanks

Muhammad Zahid

Error Renaming the Database name in Microsoft Sql Server

When I was trying the rename the database manually the Microsoft SQL Server was throwing the below error:

The database could not be exclusively locked to perform the operation.

After doing some research on this. I found out that there were some connections with that database already exists.

To resolve this we need to close all the connections first and than rename the database.

Below is the query to do all of this for us.

use master
ALTER DATABASE OldDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE OldDBName MODIFY NAME = [NewDBName]

ALTER DATABASE NewDBName SET MULTI_USER

here OldDBName is the name of your existing database and NewDBName is the name that you want to change.

More information on this can be found on the below link.

http://stackoverflow.com/questions/16685269/error-on-renaming-database-in-sql-server-2008-r2

Thanks

Muhammad Zahid.