Join Application Tables with External Database Tables

[bilbrandon]

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

[Sai_Maganti]

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-t...

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...

[rhankey]

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.

[ChristianW]

Can you explain, why you need the join? I’m afraid, that a multi database join will be slow in performance.