Batch job in ax 2009

Finance and Operations/AX Technical Forum
1

Hi all,

I have to write a batch job in Ax 2009 to create or modify data in Ax , which takes data from SQL database ( In Sql data will come from SAP database).

Need Help,

Thanks.

2

Hi,

You’re not precise but I’ll try to give leads: you can use a runbaseBatch class, this class will manage batch process, you’ll probably need to setup a business connector to communicate with SAP database to retrieve your data.

hope it helps

regards,

Thomas

3

Hi Thomas, Thanks for replying. I am sorry if my query wasnt clear, what I need is, I have to write an AX batch job that can retrieve data from SQL table say ‘SapToAx’ table and create/ modify records in AX Vendor master form or can say in vendtable. This is the first time I am writing any batch job so need some help.

4

and yes main issue here is how can I connect ax ‘Vend Table’ to sql’s ‘SapToAx’ table.

Please Help.

5

You can also use ODBC to fetch your external datas, see the following link :http://msdn.microsoft.com/en-us/library/ee677510.aspx

I think it’s more simple than configuring and using business connecto.

For your batch job, you can have a look at the tutorial_RunbaseBatch class in AOT,

regards,

Thomas

6

Thanks Thomas, Somehow I have connected Sql table with Ax database and able to pick data,

Now can you please help me writing runbatch as I am new and not much efficient in ax. Just tell what all methods need to be updated.

Please Help.

Thanks.

7

You can duplicate the tutorial_RunbaseBatch class and override a few methods, put your data processing in the run method, modify the dialog method if you have parameters to set

Regards,

Thomas

8

Hi,

I am finally able to write data from sql to ax, but I am facing some issues. Here, what I need is, if in Sql I have two records then I am not able to update Ax table. It says record already exists for ‘ID’ i.e the record which is in second row but actually there is no data in Ax table it is empty.

What I am trying to say is if my Ax table is blank and I am trying to copy data from Sql and there is only 1 record in Sql then it updates my Ax table. But If I my Sql comtains 2 rows and I am trying to copy data then it gives error " Record already exists".

Please Help.

9

Hi,

Did you set an index on your AX table? can you show the code you use to populate your AX table?

regards,

Thomas

10

Hi Thomas,

Finally I am able to update table as well.

Here is the code.

void insertdata()

{

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

VendTable vendTable, vendtable1;

str sql, criteria;

SqlStatementExecutePermission perm;

;

// Set the information on the ODBC.

loginProperty = new LoginProperty();

loginProperty.setDSN(“Test1”);

//loginProperty.setDatabase(“SapToAx”);

//Create a connection to external database.

odbcConnection = new OdbcConnection(loginProperty);

if (odbcConnection)

{

sql = “SELECT * FROM SapToAxTable”;

perm = new SqlStatementExecutePermission(sql);

perm.assert();

statement = odbcConnection.createStatement();

resultSet = statement.executeQuery(sql);

while (resultSet.next())

{

vendtable.clear();

vendtable.Currency = “INR”;

vendtable.Name = resultset.getString(1);

vendtable.VendGroup = resultset.getString(2);

vendtable.SapVendorId = resultset.getString(4);

vendtable.ContactPerson = resultset.getString(5);

vendtable.AccountNum = vendtable.SapVendorId;

//info(strfmt("%1", resultset.getString(1)));

if (VendTable::exist(vendtable.SapVendorId))

{

continue;

}

vendtable.insert();

info(“Records Created”);

}

//Close the connection.

resultSet.close();

statement.close();

}

else

{

error(“Failed to log on to the database through ODBC.”);

}

}

Now can you help me creating the batch job. ?? All other tasks are done.

Thanks.

11

Did you have a look at the Tutorial_RunBaseBatch class in the AOT?

you can try to paste the code you showed me in the run method, delete variable and parms you don’t need in your copy of the class and test.

You can have a look in the following links:

http://msdn.microsoft.com/en-us/library/aa549082(v=ax.10).aspx

http://www.axaptapedia.com/index.php?title=RunBaseBatch

Regards,

Thomas

12

yes, I have duplicated Tutorial_RunBaseBatch class. If I am trying to run the class by pressing F5 then it copies the data from Sql to ax Table, but it is not working as a batch.

What I have done is I have created as class “SaptoAx” > in this class i have created the method i had posted you(insertdata) > in main method, have called this method(insertdata).

Thanks & Regards,

Pankaj

13

And yes it is giving me errors like:

ODBC operation failed. Unable to log on to the database.

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Object ‘OdbcConnection’ could not be created

14

try to call your insertData method in the run() method, modify you insertData with server void insertData { }, a batch job must be executed on server side, modify your insertData with server void insertData() { } and call the method in the run() method, don’t modify the main method it’s the prompt() method that calls the dialog that allows you to setup the batch job in the batch tab

Regards,

Thomas

15

Hi Thomas,

Thanks for your continuous support. Finally I am have completed the functinality.

Now,

Need one more suggestion: Is it possible to change the name of AOS Instance which we have given while installation of Ax 2009??

Thanks & Regards,

Pankaj

16

Hi Pankaj,

I don’t know if you can rename an AOS instance, I think it should be possible but I don’t know how to do that.

Regards,

Thomas