SBX - Search With Button

SBX - Forum Post Title

connect to external DB using AX 2009

Microsoft Dynamics AX Forum

maram fraij asked a question on 9 Dec 2014 9:40 AM
My Badges

Question Status

Suggested Answer

I need to connect to external DB where the server is not on the domain, is it valid to use the below code or should I create DSN?

 

 

   LoginProperty    loginProperty;

    str ConnectionString;

    str UserName="";

    str Password="";

   

    ;

 

    // Set the information on the ODBC.

    loginProperty = new LoginProperty();

 

    loginProperty.setServer(prodParameters.JLV_IntegServerName);  // customized field to get server name  

    loginProperty.setDatabase(prodParameters.JLV_IntegDatabaseName);  // customized field to get DB name   

    ConnectionString=strfmt("User id=%1,Password=%2",UserName,Password);

    LoginProperty.setOther(ConnectionString);

Reply
Edwin Xavier responded on 9 Dec 2014 11:13 PM
My Badges

Hi,

   If you work AX 2009 external DB access DSN process need not. Find below coding

static void DirectSql(Args _args)

{

   CCADOConnection                  connection1;

   CCADOCommand                     command;

   CCADORecordSet                   recordSet;

   CCADOFields                      fields1;

   Com                              Coms;

   Str                              sqlQuery1;

   int                              i;

   Str                             Provider = " ";

   Str                             DataBase = " ";

   Str                             UserId = " ";

   Str                             PWD = " ";

   Str                             ConnectionString = strfmt("Provider=SqlOledb;server=" +

   Provider + ";trusted_Connection=false;" +

   "Initial catalog=" + DataBase +

   ";user id=" + userid + ";password='" + PWD + "';");

   Boolean ret,skip;

   ;

   startLengthyOperation();

   sqlQuery1      ="select * from AssetTable ";

   connection1 = new CCADOConnection();

   connection1.open(connectionString);

   command = new CCADOCommand();

   command.activeConnection(connection1);

   command.commandText(sqlQuery1);

   recordSet = new CCADORecordSet();

   recordSet = command.execute();

   Coms = recordSet.recordSet();

   while (!recordSet.EOF())

   {

      // Write Your Bussiness logic

       Coms.moveNext();

   }

   connection1.close();

   connection1 = null;

   endLengthyOperation();

}

By Using DSN

static void SQLintegrate(Args _args)

{

LoginProperty loginProp;

ODBCConnection conn;

Resultset resultSet; // get record

Statement statement; // Create SQL Statement

ResultSetMetaData metaData ; // get Record metadate like columnname

//get AOS Server Name

xSession xsession;

;

xsession = new xsession();

// Set Server Database

loginProp = new LoginProperty();

loginProp.setServer(xsession.AOSName());

loginProp.setDatabase('DSN_Name);

// Create Connection and SQL Statement

conn = new ODBCConnection(loginProp);

statement = conn.createStatement();

resultSet = statement.executeQuery("SELECT * from TableName");

while (resultSet.next())

{

metaData = resultSet.getMetaData();

info("Column Name :"+metaData.getColumnName(1)+" Value ="+resultSet.getString(1));

}

}

Thanks & Regards

Edwin.X

Reply
Abhimanyu Naruka responded on 10 Dec 2014 12:22 AM
My Badges
Suggested Answer

First of all DB's should be in same domain.

Reply
Mohamed Abdurrahman responded on 10 Dec 2014 9:41 AM

Hi maram fraij

i think you'll need a linked server.

then any option would work with you.

thanks.

Reply
Abhimanyu Naruka responded on 10 Dec 2014 12:22 AM
My Badges
Suggested Answer

First of all DB's should be in same domain.

Reply

SBX - Two Col Forum

SBX - Migrated JS