Hello -
I have a SQL data adapter that connects to an external database for data loads, dashboards, etc. Is it possible in the query to join these external tables to tables in the Application database? The server name in the error is the OneStream database server.
Thanks,
Bil
There are a couple ways of joining tables at a SQL level that are on different SQL servers. One such method is with SQL Linked Sever Definitions, which I have used extensively in the past before apps started moving to the Cloud. Of course, any method to do so depends on the servers being able to "see" one another on a common network or via some sort of VPN tunnel. If one or both ends are in the Cloud, such options might not be permitted or available. Depending on your specific requirements, you can copy the rows of the external table to a local real or temp table to which you can then use SQL Join's to local tables. Or, as has been suggested, use perform the Joining in VB.Net using Linq or to a Dictionary or any other creative way you would like.
I don't think you can as the SQL has a binding to the DB connection you have selected for the data adapter. You might have to write a BR and use LINQ to join two tables as suggested in the following post
https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Join-Framework-table-data-to-Application-table-data-using-SQL/m-p/4262#M300
Here is a nice example of LINQ join queries
https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/linq/how-to-combine-data-with-linq-by-using-joins
Can you explain, why you need the join? I’m afraid, that a multi database join will be slow in performance.
