Wednesday, November 19, 2008

Connection from AX to an External Database

There are a few options. We can create an ODBC connection on a local machine or just to connect directly without creating ODBC record.

For exmaple, we want to check whether some records exist in an external table. We should create a OdbcConnection with appropriate LoginProperty and permit to execute a SQL statement by means of SqlStatementExecutePermission class. 

server boolean checkExternalDB()
//connection parameters
LoginProperty LP = new LoginProperty();
OdbcConnection myConnection;
SqlStatementExecutePermission permission;
Statement myStatement;
str sqlStmt = "";
ResultSet myResult;
boolean ret = true;

myConnection = new OdbcConnection(LP);
info("Check connection parameters. "+funcName());
ret = checkFailed(strfmt("External DB Connection error in: %1"), #ExternalSQLDBName);

myStatement = myConnection.createStatement();
//anything you want to get from the external table
sqlStmt = "SELECT count (RecId) FROM "+#ExternalTableName+ " where "+#ExternalFieldName + " = '" + this.AccountNum+"'";

permission = new SqlStatementExecutePermission(sqlStmt);

myResult = myStatement.executeQuery(sqlStmt);
while (
if (MyResult.getInt(1) > 0)
//yes, records exist in the external table
ret = checkFailed(strfmt("@LBA53"+"\n"+funcName(), strfmt("[%1].[%2].[%3]", #ExternalSQLServerName, #ExternalSQLDBName, #ExternalTableName)));


return ret;

MS Outlook and CRM Tasks Synchronization Issue

Recently I found out an issue with synchronization of tasks between CRM module and MS Outlook in AX4.0SP2.

It looks like MS Outlook does not understand the command and causes the following error:

Method 'sort' in COM object of class '_Items' returned error code 0x80020009 (DISP_E_EXCEPTION) which means: Propriété « Start » inconnue.

I changed the synchronizeTasksOutlookToAxapta method of SmmOutlookSync_Task class in order to fix the problem like it was done in AX2009:

taskItemsCollection.sort('[Start]', false);


// Turn sort and include recurrences ON to get recurring tasks
taskItemsCollection.sort(#startDateProperty, false);