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;

1 comment:

Unknown said...

i connect my ax client with remote sqlserver database it will show errors.

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

2) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ASCENDERS\vasanth'.

3)Object 'OdbcConnection' could not be created

but it is work fine when i woked the same code in Server. I dont know how to solve the problem. plz help me with your valuable ideas..

Thanks in Advance,