Accessing SqlServer Database through X++

Finance and Operations/AX Developer Forum
1

How can i read data from Sql Server Axapta Database tables from X++ without ODBC connection?
I don’t want to create the ODBC in all the clients , so I want to access the Sql Server database from X++
without any other dependencies.

Please can anyone help me on this ?

2

//N by Arun Garg for Connecting SQL SERVER
static void DatabaseConnection(Args _args)
{

#CCADO
CCADOConnection connection;
CCADOCommand command;
CCADORecordset recordset ;
CCADOFields fields;
str Database;
str connectionStr;
str statement;
str CurrentCompanyCode;
sqlSystem sq = new sqlSystem();
CustTable custTable;
;

CurrentCompanyCode=curext();
connectionStr =" Driver={SQL Native Client};Trusted_Connection=no;SERVER= {" + sq.loginServer() + “};DATABASE={master};User Id=’’;Password=’’;
statement = “select AccountNum,Name”;
statement+=” from CustomerTable where AxaptaProcessDateTime = 0";
info(strfmt("%1",connectionStr));
info(strfmt("%1",statement));
connection = new CCADOConnection();
connection.open(connectionStr,#adConnectUnspecified);
command = new CCADOCommand();
command.activeConnection(connection);
command.commandText(statement);
try
{

recordset = command.execute();
while(!recordset.EOF())
{
fields = recordset.fields();
CustTable.AccountNum = fields.itemIdx(0).value();
//entry in PartyTable
CustTable.Name = fields.itemIdx(1).value();

CustTable.insert();

recordset.recordSet().movenext();

}

//Update Table
statement = "update CustomerTable set AxaptaProcessDateTime = getdate() where AxaptaProcessDateTime = 0 ";
command.commandText(statement);
command.execute();
info(“Records Inserted”);
connection.close();
}
catch
{
connection.close();
throw error ( "Error ");

}

}

3

See How to connect SQL SERVER with Dynamics Ax 2009

4

you got ur answer

5

Hi,

You may use Connection class. By default this will establish a connection to the current database session. Code snippet from Developers guide below. Also search in this forum as this subject was already discussed.

server static void main(Args args)
{
    Connection con = new Connection();
    Statement stmt = con.createStatement();
    ResultSet r;
    str sql;
    SqlStatementExecutePermission perm;
    ;
 
    sql = strfmt('SELECT VALUE FROM SQLSYSTEMVARIABLES');
 
    // Set code access permission to help protect the use of
    // Statement.executeUpdate.
    perm = new SqlStatementExecutePermission(sql);
    perm.assert();
 
    try
    {
        r = stmt.executeQuery(sql);
        while (r.next())
        {
            print r.getString(1);
            pause;
        }
    }
    catch (exception::Error)
    {
        print "An error occured in the query.";
        pause;
    }
    // Code access permission scope ends here.
    CodeAccessPermission::revertAssert();
}
6

When I try this code I get the following error message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.”

Can anyone help me with this ?

7

Hi Harish,

I am calling stored procedure using connection class nd using executeupdate to execute the stored procedure.In my stored procedure I am calling the view of another database and have full rights on another database. looks like sql connection is timing out and inserting very few records than expected. Same stored procedure with same rights inserts more records when called from sql server.is there any way to increase the query timeout of sql connection

Thanks,

Ashish