Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Connection string of SQL server staging database to d365 on premise

(0) ShareShare
ReportReport
Posted on by 410

I want to know how connect to staging database created in SQL server of same domain in which d365 for finance and operations is installed.

Now I want to pull data from staging database tables

And push data from d365 to staging database tables.

Please help me.. this is my first d365 project

*This post is locked for comments

  • Raheel Khan Profile Picture
    Raheel Khan 40 on at
    RE: Connection string of SQL server staging database to d365 on premise

    "data import and export, including recurring integrations is supported" is this available for on perm ?

  • rajeev tiwari Profile Picture
    rajeev tiwari 410 on at
    RE: Connection string of SQL server staging database to d365 on premise

    Dear Martin,

    I have not tried connecting other application.

    Let me explain you architecture of my application

    D365 SQL DB <->D365 AOS Servers <-> Staging DB <-> Two External Applications.

    Staging DB and two external applications will remain as it is and only legacy system will be replaced by D365 on premise. Staging DB is in SQL Server.  

    Now I have to read data from staging Db to D365 and also export data from d365 to Staging DB.

    For this I need ODBC connection string and also is there any other way to read and write data from staging SQL DB to D365. 

                                               

     

     

                                                                                                                                                                                           

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Connection string of SQL server staging database to d365 on premise

    How (if) have you configured the ODBC data source? Is the database name correct? What about authentication? Are you able to connect there from other applications than AX?

  • rajeev tiwari Profile Picture
    rajeev tiwari 410 on at
    RE: Connection string of SQL server staging database to d365 on premise

    I am trying to connect SQL Database from D365 (on premise)  but I am not able to connect to SQL Database , It is giving exception message,

    Microsoft.Dynamics.Ax.Xpp.InternalException was unhandled by user code

    Message: An exception of type 'Microsoft.Dynamics.Ax.Xpp.InternalException' occurred in AOSKernel.dll but was not handled in user code

    Additional information: Object 'OdbcConnection' could not be created

    I have written code for testing connection: Please suggest how to connect. 

    public static void main(Args _args)
    {

    LoginProperty loginProp;
    ODBCConnection conn;
    Resultset resultSet, resultSetCount;
    Statement statement1, statement2;
    ;

    loginProp = new LoginProperty();

    loginProp.setServer("MININT-TE88M28");
    loginProp.setDatabase("Test");

    conn = new ODBCConnection(loginProp);

    statement1 = conn.createStatement();
    resultSet = statement1.executeQuery("SELECT * from item");

    while (resultSet.next())
    {
    info(resultSet.getString(1));
    }
    }

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Connection string of SQL server staging database to d365 on premise

    To make it clearer, data import and export, including recurring integrations is supported. I meant only the ODBC source when talking about an unsupported feature.

    I can't answer what is the best way, because there simply isn't a single best way for all possible scenarios. I would consider several different options. Some of them are mentioned above, some would question your previous decisions such as "using x++".

  • rajeev tiwari Profile Picture
    rajeev tiwari 410 on at
    RE: Connection string of SQL server staging database to d365 on premise

    Hello Martin,

    Thanks for your suggestion ..

    Which is the best way to read data from SQL Server Staging Database.

    I am using D365 on Premise.

    Thanks 

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Connection string of SQL server staging database to d365 on premise

    Aha, so you want to do it from AX. While using OdbcConnection class (as mentioned by Skurut) is possible, I wouldn't do it except of maybe some trivial cases. .NET offers much easier and safer ways - you can generate classes from schema of the database and use classes and method in strongly-typed way, which completely removes whole classes of errors. You can see one possible way in my blog post Connection to external database. It was for AX 2012; working with other VS projects is even easier in AX 7.

    An even better solution would be using Data Import Export Framework with ODBC, but it's still not implemented, as far as I know.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,817 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans