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
"data import and export, including recurring integrations is supported" is this available for on perm ?
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.
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?
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));
}
}
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++".
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
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.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,817
Most Valuable Professional
nmaenpaa
101,156