Cross server Query

[smpa01]

TLDR: Is it at all possible to write cross SQL server queries in Azure SQL database (resulting from datamart)?

I have two AZURE Sql servers as server1 and server2; respectively resulting from Datamart1 and Datamart2. Is it at all possible to write cross-server queries such as

//pseudoquery
SELECT 
  server1_db_dimTbl.CustomerID, 
  server1_db_dimTbl.OrderId, 
  server2_db_factTbl.CustomerName, 
  server2_db_factTbl.Company 
FROM 
  server1_db_dimTbl 
  INNER JOIN server2_db_factTbl ON server1_db_dimTbl.CustomerID = server2_db_factTbl.CustomerID

I tried ElasticQueries as well as OPENROWSET ; such as

//tried in server1_db
SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=blahblah.datamart.fabric.microsoft.com;Trusted_Connection=yes;',
    model.fact
) AS d;

//getting
// 'OPENROWSET' rowset provider not supported in this version of SQL Server.

I could not make any of it to work. I am not sure what I am doing wrong. But is it possible at all?

Thank you in advance.

@parry2k @AlexisOlson @bcdobbs @GilbertQ @d_gosbell

@ImkeF @cwebb 

[AlexisOlson]

I'm not sure you can do this in SQL but you should still be able to combine them with a dataflow.

[hackcrr]

Hi, @smpa01 

Maybe you can try Elastic Queries or Azure Data Factory (ADF) or Azure Synapse Pipelines:

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview?view=azuresql

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[GilbertQ]

Hi @smpa01 

As far as I know it is not possible to cross-query Azure SQL Databases.