How to execute SQL directly form Dynamics AX X++

How to execute Sql directly form Dynamics AX X++

Reference by : alirazazaidi

Dynamics Ax provide many other ways to communicate with database, but sometimes we have to execute direct query to database. For example I have to delete all items form InventTable of particular company or dataArea. I execute it as follow.

Connection      connection;

Statement       statement;

str             query;

DataAreaId      currentDataArea; 

// create connection object

connection = new Connection();

// create statement

statement = connection.createStatement();

//Find current company

currentDataArea = curext();

if(! Box::yesNo(“You are in Company: ” + currentDataArea + “. Proceed to Delete?”,DialogButton::No))

{

return;

}

//Vendors delete

query = “delete from inventTable where dataAreaId = ‘” + currentDataArea + “‘”;

new SqlStatementExecutePermission(query).assert();

statement.executeUpdate(query);

CodeAccessPermission::revertAssert();

info(“InventItems Deleted from Company: ” + currentDataArea);

Report deployment error "No report data table with name exists in report schema for data provider"

ISSUE:

When you deploy the report in Dynamics AX 2012 or D365F&O and you try to view the report and get the error "no report data table with name exists in report schema for data provider".

SOLUTION:

Just restart your report server service from the services console and the error will be resolved.

Thank you,

Error Cannot create a record in Entity (DMFEntity)

Solution:

Simply delete all records in DMFEntity table.

Note: delete only the entity which you are working on and then rebuild and sync your project.

There are a lot of other related error:

Severity    Code    Description    Project    File    Line    Suppression State

Error        Infolog diagnostic message: 'Error synching entity metadata for entity - KRTProductEntity. Message -    at Microsoft.Dynamics.Ax.MSIL.Interop.throwException(Int32 ExceptionValue, interpret* ip)            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityUpdate(DMFEntity dmfEntity, DictDataEntity dictDataEntity)            0    

Error        at Dynamics.AX.Application.DMFEntity.insert() in xppSource://Source/ApplicationFoundation\AxTable_DMFEntity.xpp:line 433            0    

Error        Infolog diagnostic message: 'Cannot create a record in Entity (DMFEntity). Entity: Items, KRTProductStaging.            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityUpdate(DMFEntity dmfEntity, DictDataEntity dictDataEntity, Boolean useTargetEntityName, Boolean @useTargetEntityName_IsDefaultSet) in xppSource://Source/ApplicationFoundation\AxClass_DmfDataPopulation.xpp:line 947            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityMetadata(StringCollection dataEntityViewCollection, StringCollection compositeEntityList) in xppSource://Source/ApplicationFoundation\AxClass_DmfDataPopulation.xpp:line 794.' on category 'Error'.            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityCreate(DictDataEntity dictDataEntity, Boolean useTargetEntityName, Boolean @useTargetEntityName_IsDefaultSet) in xppSource://Source/ApplicationFoundation\AxClass_DmfDataPopulation.xpp:line 899            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityCreate(DictDataEntity dictDataEntity, Boolean useTargetEntityName)            0    

Error        at Microsoft.Dynamics.Ax.Xpp.Common.Insert()            0    

Error        at Microsoft.Dynamics.Ax.MSIL.cqlCursorIL.insert(IntPtr table)            0    

Error        at Microsoft.Dynamics.Ax.Xpp.NativeCommonImplementation.Insert()            0    

Error        Database synchronization failed. You may have to do a full build of the package 'K3Retail' and all of its dependent packages.            0    

Error        The record already exists.' on category 'Error'.            0    

class DeleteDMFEntity

{        

    public static void main(Args _args)

    {      

        DMFEntity dmfEntity;

        DMFTargetXML dmfTargetXML;

 

         //ttsbegin;

        delete_from dmfEntity;

        delete_from dmfTargetXML;

 

         /*while select forupdate dmfEntity

        {

            dmfEntity.delete();

        }*/

        //ttscommit;

        info('Done');

    }

}

Thank you,

X++ function date2Str()

 Hi,

This post is about the X++ function date2Str(). This Function Converts the specified date to a string.

http://msdn.microsoft.com/en-us/library/aa857241.aspx

str date2Str(

    date date,

    int sequence,

    int day,

    int separator1,

    int month,

    int separator2,

    int year

    [, int flags = DateFlags::None])

Parameters

Parameter

Description

date

The date to convert.

sequence

A three digit number that indicates the sequence for the components of the date, 1 for day, 2 for month, and 3 for year.

day

A DateDay enumeration value that indicates the format for the day component of the date.

separator1

A DateSeparator enumeration value that indicates the separator to use between the first two components of the date.

month

A DateMonth enumeration value that indicates the format for the month component of the date.

separator2

A DateSeparator enumeration value that indicates the separator to use between the last two components of the date.

year

A DateYear enumeration value that indicates the format for the year component of the date.

flags

A DateFlags enumeration value that indicates whether the language settings on the local computer should be used to calculate the proper left-to-right or right-to-left sequence in the returned string.

Return Value

A string that represents the specified date.

X++ Example:

static void Job2(Args _args)

{

    date currentDate = today();

    str s;

    int iEnum;

    ;

    s = date2Str

        (currentDate,

        321,

        DateDay::Digits2,

        DateSeparator::Hyphen, // separator1

        DateMonth::Digits2,

        DateSeparator::Hyphen, // separator2

        DateYear::Digits4

        );

    info("Today is:  " + s);

}

/** Example Infolog output

Message (12:36:21 pm)

Today is:  2009-01-13

**/

Thank you,

Calculate Days and Month of Date - X++

Hi,

In this post we will be related to days and month of Date.

In below sample of code you to get example for following point:

    1. Start date of the month.

    2. End date of the month.

    3. Total number of days in the month.

    4. Remaining number of days in the month.

static void days_Example(Args    _args)

{

    int FromDays, ToDays;

    info(strFmt("Today date is %1", today()));    

    

    // Displays start date of the month for the given date.

    info(strFmt("Start date is %1 for the month of %2", date2str(DateStartMth(Today()),213,2,4,2,4,4), mthName(mthOfYr(today()))));

    // Displays end date of the month for the given date.

    info(strFmt("End date is %1 for the month of %2", date2str(endmth(Today()),213,2,4,2,4,4), mthName(mthOfYr(today()))));

    // Displays total number of days in the month for the given date

    FromDays = date2num(DateStartMth(today()));

    todays = date2num(endmth(today()));

    info(strFmt("There are total %1 days in month %2", (ToDays+1) - FromDays, mthName(mthOfYr(today()))));

    // Displays remaining number of days in the month for the given date

    FromDays = date2num(DateStartMth(today()));

    todays = date2num(today());

    info(strFmt("%1 days are remaining in the month of %2", (ToDays+1) - FromDays, mthName(mthOfYr(today()))));

}

Thank you,

Accessing Data of other companies/Legal entities using X++

Hi,

In Microsoft Dynamics AX/D365 Finance and operation, users can setup multiple legal entities. By default in Microsoft Dynamics AX/D365 FO, all tables store data per company unless SaveDataPerCompany property is not changed to NO. By default value of property SaveDataPerCompany  = Yes.

Using crossCompany & changeCompany keyword, you can construct a query to retrieve all records, regardless of the company you are currently logged into. You can fetch the all the company records using keyword - crossCompany and function – changeCompany.

Change-Company:

static void main()

{

  CustTable custTable;

  changeCompany('INMF') 

  {

    custTable = null;

    while select custTable

    {

       //custTable of company 'INMF'.

    }

  }

}

Cross-Company:

static void main()

{

  CustTable custTable;

  while select crosscompany custTable

  {

      //custTable of all the companies

  }

}

Thank you,

D365F&O / AX7 URL parameters

Hi All,

This post is regarding different type of D365F&O URL parameters.

1. Calling a Display – MenuItem directly via the ''mi'' Parameter

With the ''mi'' parameter you can call a (Display) MenuItem directly. The example shows how to call the ''SalesTableListPage'' directly.

Call: ?cmp=[Company]&mi=[MenuItemName]

Example: MenuItemName -> SalesTableListPage

2. Open a Form via an URL Parameter

If you want to call a Form directly, use the parameter "f".

Call: ?cmp=[Company]&f=[FormName]

Example: MenuItemName -> ProdParameters

3. See your Data in the Built-In Table Browser

The very convenient built-in table browser can be called from the address bar as well. Just use the "mi" parameter from above and add the parameter "tablename", afterwards add the name of the table you want to see.

Call: ?cmp=[Company]&mi=SysTableBrowser&tablename=[TableName]

Example: TableName -> SalesTable

4. Run a Class directly from your Browser

You can also call a runnable class. To do so, add the "SysClassRunner" menu item and the parameter "cls" with the class you want to call.

Call: ?cmp=[Company]&mi=SysClassRunner&cls=[SysClassRunnerExample]

class SysClassRunnerExample

{        

    /// <summary>

    /// Runs the class with the specified arguments.

    /// </summary>

    /// <param name = "_args">The specified arguments.</param>

    public static void main(Args _args)

    {        

        info("From Classrunner!");

    }

}

5. Change the language on the fly

Changing the language is also an option that we can change from the address bar, just add the parameter "lng" and pass the language you want to change AX to.

Call: ?cmp=[Company]&lng=[en-us]

Thank you