Import from SQL table(not axapta) to Axapta tables

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

Hi, Personally, I would like to write a job in Axapta. -Read data from external database using class ODBC. Here is the sample code: static void ODBCRead(Args _args) { LoginProperty LP = new LoginProperty(); OdbcConnection myConnection; Statement myStatement; ResultSet myResult; ; LP.setDSN(“Northwind”); myConnection = new OdbcConnection(LP); myStatement = myConnection.createStatement(); myResult = myStatement.executeQuery(“SELECT * FROM Customers”); while (myResult.next()) { info(strfmt("%1 %2", myResult.getString(1), myResult.getString(2))); } } -insert data to Axapta and include all the logic that may have. Hope this can help.

thank you for your response, i have used what you suggested, it worked. But using the same objects is it possible to call stored procedure, instead of ExecuteQuery or ExecuteUpdate? I need also create another process, which after table in Axapta is populated from ImportTable(not Axapta table), update some fields in ImportTable. I would rather do it in stored proc, using “Update ImportTable select …from Axapta table”, it would be much more effective, that looping again thru records. Do u know the way to call stored proc? thank you.

Hi I tried using Store Proc in Axapta sometimes ago, but I did not get too far as Axapta doesn’t support developer to call SProc in standard. You may want to look at the ADO connection class (try “find” method on AOT to get the class name) and find out how to call a SP. Regards,

Hi, You can execute a stored proc from Axapta like - Connection Con = new Connection(); Statement Stmt = Con.createStatement(); ResultSet R = Stmt.executeQuery('<STORED_PROCEDURE_NAME>'); while (R.next()) { print R.getString(1); } I have used stored proc from Axapta in the past to import data from another SQL database into Axapta. Overall it works ok. But there are quite a few drawbacks using this route. Regards, Harish Mohanbabu

Guys can any body Reply me for, how to insert a record in Sql Server from Ax4.0 with out using Axapta Table.

Thanks in advance!

sudvaep

Hi Sudvaep,

I guess you want to write to external SQL tables from Ax 4.0. I would write a stored procedure in SQL and call that from Ax. For example


sqlStat = strfmt("%1 %2", , curext());

stmt.execute(sqlStat);


In the above string, I am formatting the stored procedure name along with an input parameter. I like using stored procedure as I find it an elegant option. But there is no reason why a SQL statement can’t be used instead.

Hope this helps,

Hi,

Sorry to ask you this question but I am not able to fine the LoginProperty, ODBCConnection, Statement and ResultSet classes in my AOT. When using these on the time of compiling it gives me the error says “Variable LoginProperty has not been declared.”.

Please help me out for this.

Thanks,

Ashlesh

hi,

this approach works but i have problem returning images from the external sql table, can u pls suggest how to get image type field from external SQL tables.

regards

Akshay

Hi,

The job serves it purpose of importing data in AX from external database. But is it necessary to create a DSN for the same? can this be done without using DSN, if yes then how to do it?

Hi Snehal,

If u dont want to use DSN the u can directly give connection string using ADO.NET classes

Eg:

declare

System.Data.SqlClient.SqlConnection connect;

and give connection string like

connect = new System.Data.SqlClient.SqlConnection( );

connect.Open();

This will create connection with SQL Server external Database

Regards,

Kiran.S

Hi Kiran,

Thanks

Hi,

I am working on establishing a connection between AX(2012) and external dB , but i am not able to find the CCADOConnection class in AX 2012…

Is it that there is no such class in 2012 ? If yes, then what would be the alternative for it ?

Request if you could revert back at the earliest as its a priority task.

Thanks in advance

Regards,

Snehal