2 Nov’15

Dynamics AXConnect to an external database using X++ code in AX 2012

Below are the steps to be performed,

  1. To connect to an external database using ODBC connection, first create a DSN. To create a DSN refer the link https://support.microsoft.com/en-us/kb/300596
  2. Write the below code:

    1

    Note : The database login credentials are given in the DSN. You can use windows credentials or provide SQL credentials, if required.

  3. In the above code, the below lines of code will initialise the connection to the database.
        sq = new SqlSystem();
        loginProperty = new LoginProperty();
        loginProperty.setDSN(/*your dsn name here*/);
        loginProperty.setDatabase(/*your database name here*/);
        oDBCConnection = new ODBCConnection(loginProperty);
    
  4. The below lines of code will execute the query and store the data in a resulSet.
        sqlStmnt = strFmt("select * from ABC"); //write your query here
        statement1 = odbcConnection.createStatement();
        perm1 = new SqlStatementExecutePermission(sqlStmnt);
        perm1.assert();
        myResult1  = statement1.executeQuery(sqlStmt1);
    
  5. We can also execute the stored procedures in the database. Write the below code to do so.
        odbcConnection = new OdbcConnection(loginProperty);
        sqlStmt1 = strFmt("exec [Stored Procedure name]"); 
        perm = new SqlStatementExecutePermission(sqlStmt1);
        perm.assert();
        statement1 = odbcConnection.createStatement();
        statement1.executeUpdate(sqlStmt1);
        CodeAccessPermission::revertAssert();
    
  6. Suppose you execute more than one stored procedure in the same code, you may get below error.
    "SQL error description: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt"
    
  7. To eliminate this error, write below statement after every stored procedure is executed,
    statement1.close();
    
  8. If there are multiple external databases on different servers, then you can create separate DSN to connect to each server. This connection information can be stored in a master table containing the DSN name, the database name.
  9. The X++ code can then use the connection details from the master table as shown below.
    loginProperty = new LoginProperty();
        loginProperty.setDSN(masterTable.databseName);
        loginProperty.setDatabase(masterTable.databseName);
    

 

Written by

Team Member

CloudFronts

One thought on “Connect to an external database using X++ code in AX 2012”

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more