I have two databases, one in SQL Server and one in Azure SQL Synapse. Now I want to have one single query that should be done between two tables from these different servers.

For example:

• SQL Server name : server1

• Database name : db1

• Table name : tb1

• Azure Synapse name : prod-synapse-2

• Database name : db2

• Table name : tb2

Now query should be like this:

select 
    tb1.col1, tb1.col2, tb2.col3, tb2.col4 
from 
    tb1 
outer join 
    tb2 on tb1.col5 = tb2.col5

The above query is very simple to join two tables from the same database or two tables from the 2 different database within same server.

But I want suggestions to have the above query between 2 different servers.

I have seen in many stackoverflow questions similar to this, they suggested an answer using Linked Server, but that cannot be done in my case, because I will not be provided with access to create Linked server, and link the 2 servers in Microsoft SQL Server Management Studio.

Thanks in advance

CodePudding user response：

Another way to query a database hosted in a remote SQL Server is the OPENROWSET T-SQL function. To use the OPENROWSET ad hoc method, you need to provide all connection information that is required to connect to the remote SQL server and many other resources.

Using OPENROWSET requires enabling the Ad Hoc Distributed Queries advanced configuration option same as the OPENDATASOURCE function.

You need to provide the provider’s name, the connection string and the query as follows:

OPENROWSET(‘providername’,’datascource,’query)