Error: Cannot create a record in . The record already exists

Thanks to Amer Atiyah.....:)
Source : Amer Atiyah's Blog

Sometimes you might face an unexpected error when trying to insert new records to any of the Dynamics AX tables. The message you might have is like: Cannot create a record in Journal lines (LedgerJournalTrans). The record already exists.

You might also have tried to check the indexes of this table that shouldn’t be duplicated. And you got surprised when you are not violating those constraints, so there is no any rational reason why this error might appear.

After so many tries, I got to fix that error by:

1. Backing-up my database (just in case that anything went wrong)
2. Exporting the data of that table (from the AX Import/Export functionality)
3. Deleting/Dropping the table from the Microsoft SQL Server Management Studio (by this all the data of course will be deleted)
4. Opening the Dynamics AX client, going to: AOT –> Data Dictionary –> Tables –> LedgerJournalTrans –> Right clieck –> Synchronize
5. Importing the data again to all companies (from the AX Import/Export functionality.

As you could see, I got this error in a very critical table that is the LedgerJournalTrans table. This table contained already posted lines and it was really headache for me to get it fixed. But fortunately I was able to fix it by following the previous steps. 

Sales Table to sales line update

The SalesTable2Line framwork is used in AX to update fields on the SalesLine when the corresponding field in the SalesTable was changed e.g. DevliveryMode. Follow these steps to extend the framework for your own fields.

• Create a new field in the SalesTable and SalesLine e.g. a SalesNote field
• Add the field to the FieldGroup HeaderToLineUpdate in the SalesTable
• Display the new fields in the SalesTable form
• Delete all records from the SalesTable2LineParameters table
• Open Accounts Receivable > Settings > Parameter > "Update order line"
• Extend the SalesTable2LineField.lineUpdateDescription method

case fieldnum(SalesTable, SalesNote): 
    return fieldid2pname(tableNum(SalesLine), fieldNum(SalesLine,SalesNote));

• Add parm methods for the new field to AxSalesTable and AxSalesLine classes

public SalesNote parmSalesNote(SalesNote _salesNote = “) 
{ 
    if (!prmisdefault(_salesNote)) 
    { 
        this.setField(fieldnum(SalesLine, SalesNote), _salesNote); 
    }
 
    return salesLine.SalesNote; 
}

• Create a set method in the AxSalesLine class

protected void setSalesNote() 
{ 
    if (this.isMethodExecuted(funcname(), fieldnum(SalesLine, SalesNote))) 
    { 
        return; 
    }
 
    this.setAxSalesTableFields();
 
    if (this.isAxSalesTableFieldsSet() || this.axSalesTable().isFieldModified(fieldnum(SalesTable, SalesNote))) 
    { 
        this.parmSalesNote(this.axSalesTable().parmSalesNote()); 
    } 
}

• Add the call of this set method in the AxSalesLine.setTableFields method

Reports Unpack Method raised the Debugger mode with error "Wrong Argument types in variable.."

I was updated the AX 2009 RU6 with  RU8 and as the users checked user reports; the error and debugger for unpack method is raised with the following description.


'Wrong Argument types in variable assignment'


This error can also be raised if you are importing any report to the AOT.


To Resolve the error i used following action for particular user.
1. Go to Administrator
2. Open Users form and select the user
3. Select User Options.
4. Select Usage Data
5. Click on the Reset button.

This will reset all the temp data of selection,range etc.

Cannot execute a data definition language command on (). The SQL database has issued an error.

You might get this error message following an upgrade:

Cannot execute a data definition language command on (). The SQL database has issued an error. Problems during SQL data dictionary synchronisation. The operation failed. Synchronise failed on n table(s)

To find out exactly what the problem is you will need to have a look in the Windows event viewer on the AOS server, which will look something like this:

If the error is similar to this (I.E of the family “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found”) then you have the following options to solve the problem:

1. If you are not worried about the data in the table then the simplest solution is to drop the table

Otherwise:

2. In AX, temporarily disable the index (that causes the duplicate) and use the table browser with filters(using data from the event log) to correct the data.

or

3.  Use SQL Server management studio to resolve the problem – see this microsoft knowledge base article for a great guide on how to do this: http://support.microsoft.com/kb/139444

=================================================================

=================================================================

Run the below job , in order to get the table name

static void forceDbSynchronize(Args _args)

{

    Dictionary              dict;

    int                     idx, lastIdx, totalTables;

    TableId                 tableId;

    Application             application;

    SysOperationProgress    progress;

    StackBase               errorStack;

    ErrorTxt                errorTxt;

    ;

    application = new Application();

    dict = new Dictionary();

    totalTables = dict.tableCnt();

    progress = new SysOperationProgress();

    progress.setTotal(totalTables);

    progress.setCaption("@SYS90206");

    errorStack = new StackBase(Types::String);

    lastIdx = 0;

    try

    {

        for (idx = lastIdx+1; idx <= totalTables; idx++)

        {

            tableId = dict.tableCnt2Id(idx);

            progress.setText(dict.tableName(tableId));

            lastIdx = idx;

            application.dbSynchronize(tableId, false, true, false);

            progress.incCount();

        }

    }

    catch (Exception::Error)

    {

        errorTxt = strFmt("Error in table '%1' (%2)", tableId, dict.tableName(tableId));

        errorStack.push(errorTxt);

        retry;

    }

    setPrefix("@SYS86407");

    errorTxt = errorStack.pop();

    while (errorTxt)

    {

        error(errorTxt);

        errorTxt = errorStack.pop();

    }

}

How to put Ax 2009 machine back to a clean state (remove the usr layer)

1. Stop the AOS (default service name is AOS50$01 for AX5).


2. Delete the user layer aod file (…\Microsoft Dynamics AX\50\Application\Appl\AXDB_App\axusr.aod) and *.aoi files.


3. Delete the metadata file in client %userprofile%\AppData\*.auc


4. Start the AOS.


5. Run AX client and do a synchronize on the Data Dictionary node on the AOT tree.

Reporting Extension Server URL Validation problem

1. 1.    Check the Reporting Services (Started or not) from the services.
2. 2.       Check the reporting services configuration manager from SQL server 2008 configuration tool and add service account as business connector account.
3. 3.       Browse the Reportserver and Report manager URL from RS configuration manager.
4. 4.       Copy the URL and paste same into the above reporting servers window.
5. 5.       (You can also check that weather your url in Reporting server is right or not.
6. Update URL https to http.