Transfer data from Axapta tables to SQL table

Hi,

I need to transfer data from Axapta tables to SQL table which is populated by other process(outside of axapta application)
How to do that?
This table could not be on the same server where Axapta db is.

thank you

You could use OdbcConnection class for this. Read Chapter 2, Data Integration from Dynamics Ax 2009 Training Manual DEV III.

Hi,

Thank you.

I am already using OdbcConnection class. i am getting the the AX data from AX Table using OdbcConnection. But, i can’t Put the data to other DataBase(Not AX Table). Please help me about How to put data to other database(Not AX Table).

Better use business connector in the following way

  1. read the data from Axapta Table by Ax business connector

  2. write the data into table of another database using its connection string

Hi,

Thank you for replying. But i am already use connection string for read data from AXapta Table and .

But i don’t know how to use connection string for write the data into table of another database.Please help me

Thank You

my solution was to create a visual studio console or windows project (C# or VB.NET) and then read the data from ax and write/insert to table of another database.

my solution was to create a visual studio console or windows project (C# or VB.NET) and then read the data from ax and write/insert to table of another database.

you should read the data from ax and write/insert to table of another database.
and it will take time…!

Hi,

As you saw from this post, there are many options available. Which option you choose is purely based on your requirement, existing skill set in your organisation etc.

But In the past I wrote SQL stored procedures for this purpose and then scheduled SQL jobs to execute stored procs at regular intervals.

Hi Harish,

Thanks for replying, i know about SQL stored procedures. But, I want to do everything in X++.

I want to generate a new converter in X++ . The below code is working fine But now i want to check one by one EMPLID to CODE.

If EMPLID is not found in EP004 then EMPLID is insert into CODE field in EP004(EP004 is other database Table)

static void ODBCReadNew(Args _args)

{

LoginProperty loginProp;

Statement statement;

str sqlStmt;

SqlStatementExecutePermission permission;

LoginProperty LP = new LoginProperty();

OdbcConnection myConnection;

Statement myStatement=null;

ResultSet myResult,rs;

EmplTable emp; //Axapta database Table Name

str numebr;

;

while select * from emp

{

numebr= emp.EmplId;

print emp.EmplId;

}

LP.setDSN(“ep”);

myConnection = new OdbcConnection(LP);

//sqlStmt = strfmt(“UPDATE dbo.EP004 SET REVIEW_DAYS=11”);

sqlStmt = strfmt(“update dbo.EP007 SET CODE=’%1’where ID =3”,numebr);

// Check if it’s ok to use sql statement

permission = new SqlStatementExecutePermission(sqlStmt);

permission.assert();

// Create and prepare a new Statement object

// from the connection

myStatement = myConnection.createStatement();

// Execute the query and retrieve the resultset

myStatement.executeUpdate(sqlStmt);

// Revert the permission assert

CodeAccessPermission::revertAssert();

pause;

}

Darhana,

Did u get solution for that?

I need …

Thanks in Advance

Manikkam.

I used this for inserting into a SQL Server table in a different database. I didn’t work out the exact problem, but it seemed to work once I created a separate function for doing the inserts that were mixed in with SELECT commands.

public

static str ZInsertIntoB(str _sqlInsertStatement)
{
str error;
Connection connection;
Statement conStatement;

try
{
connection = new Connection();
conStatement = connection.createStatement();
new SqlStatementExecutePermission(_sqlInsertStatement).Assert();
conStatement.executeUpdate(_sqlInsertStatement);
CodeAccessPermission::revertAssert();
}
catch
{
error = 'An unknown error occurred while trying to execute the following statement: ’ + _sqlInsertStatement;
}

return error;
}