Create instant copy from another SQL Server to AX table

(0) Share

Report

Posted on by Community Member

Hi,

i have a table in another sql server that has attendance records in and out

so i need to copy these records instantly to table in AX without duplication in records and just copy the new records to ax table

how i can do that

first i tested the connection between ax and another sql server and it working fine by next code

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("sccm");
    loginProperty.setDatabase("ReportServer");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        // Set the select statement to the string variable
        sql = 'SELECT * from Users';


        //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())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

now how i do it by automatic way ..
may be this way is wrong to copy the data so i am waiting and suggestions..

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

You should be using Data Import eXport Framework (DIXF), which supports ODBC sources too, for creating records in AX in a repeatable way.

[View:https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/lifecycle-services/ax-2012/user-guide-dixf:750:50]

There are out-of-the-box data entities, but for custom tables or less frequently used standard ones you might have to create your own custom data entity.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'll test it now

Was this reply helpful? Yes No
Suggested answer

Umair Qaisar on at

Like (0)

Report

When you said 'instantly', I am assuming you want to keep AX tables synced with outside SQL table. If this is true, then you need MDM (Master Data Management). You should get some help from below two link:

technet.microsoft.com/.../dn720451.aspx

community.dynamics.com/.../microsoft-dynamics-ax-2012-master-data-management-scenario-part-2

All the articles I read about MDM in AX, they have Master and Slave both AX. In your case, you have Master non-AX table. In theory, it should work because MDM in AX utilizes SQL Server's MDM component which is needed to be installed separately.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

MDM for AX relies on Data entities and the master-slave setup is meant to be between AX databases, unless you do time and development investment to get it working with an alternate source. It is best to rely on out-of-the-box tools such as DIXF or Document Services. If you want it to synchronize "instantly", you could do that by kicking off the DIXF batch to pull that information via a web service or business connector just fine.

Was this reply helpful? Yes No