Forum Discussion

danszczepanski's avatar
danszczepanski
New Contributor III
2 days ago

Reference External DB and Application DB in one SQL Query

Hello,

I am writing a SQL Query that references tables in different databases, one that exists in the External database (BI Blend) and one that exists in the Application database. I am having trouble setting up my data adapter to allow for pulling in both tables to perform a Union, as I need to specify the Database Location. Is this possible?

Analytic Blend
  • sameburn's avatar
    sameburn
    21 hours ago

    Hi danszczepanski

    As Teresa and Daniel have pointed out. You can achieve this with a Dashboard DataSet BR.

    You would need to execute both sql commands separately e.g datatable1 is from application database and datatable2 is from framework database

    You can then use the DataTable Merge method (assuming both datatables have the same columns as per your union statement) to merge the two datatables.

    Then return the merged DataTable to your Data Adapter. 

    https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.merge?view=net-9.0&redirectedfrom=MSDN#overloads

    Hope this helps

  • T_Kress's avatar
    T_Kress
    Contributor III
    2 days ago

    My understanding is that it is an Azure limitation that you cannot SQL across two different databases.  If your customer or you are on the Azure Cloud, then no this will not be possible in a data adaptor.  There are work arounds via BRs that open two database connections and then run each SQL separately and joins via BR, but not data adaptor.

  • DanielWillis's avatar
    DanielWillis
    Valued Contributor
    2 days ago

    Hi danszczepanski 

    As T_Kress mentioned the way to do this is by utilising a Business Rule. You will need to change the Data Adapter to Command Type: Method, Method Type: Business Rule instead of SQL. Can't remember if it gives an example of the method query syntax there but if not it should be in the docs and forums.

    Your business rule will be a Dashboard Data Set business rule which returns a datatable (or is it a DataSet? You can query each database separately into data tables and then join them into a single datatable using .NET commands.

    I think you should be able to search forums and the web for how to achieve the various bits and pieces from above. The docs might have some examples particularly around the method queries.

    Regards,

    Daniel

    • danszczepanski's avatar
      danszczepanski
      New Contributor III
      14 hours ago

      Thanks, Daniel! This was the route I began to go with. I will follow up if I have any further questions.

  • sameburn's avatar
    sameburn
    Contributor II
    21 hours ago

    Hi danszczepanski

    As Teresa and Daniel have pointed out. You can achieve this with a Dashboard DataSet BR.

    You would need to execute both sql commands separately e.g datatable1 is from application database and datatable2 is from framework database

    You can then use the DataTable Merge method (assuming both datatables have the same columns as per your union statement) to merge the two datatables.

    Then return the merged DataTable to your Data Adapter. 

    https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.merge?view=net-9.0&redirectedfrom=MSDN#overloads

    Hope this helps