How to create DSN and connect to an External Database from X++ in AX 2012

Go to Administrative Tools  >>  Data Sources (ODBC)

In the tab User DSN >> Add >> Choose SQL Server Native Client

Config DSN to your database and server
The following X++ code example uses ODBC to connect to an external database:

    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;
    // Set the information on the ODBC.
    loginProperty = new LoginProperty();    
    loginProperty.setDSN("BaoHuynh");
    
    loginProperty.setDatabase("DBName");
    
    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM DBName WHERE FIELD = "
            + your criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {           
            //Always get fields in numerical order, such as 1 then 2 the 3 
            print resultSet.getString(1);
            print resultSet.getString(2);
            print resultSet.getString(3);
        }
        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");

    }