OPENDATASOURCE (Transact-SQL) - Connecting to multiple tables


I have a use case for OPENDATASROUCE. However, my SQL query has multiple tables with left joins. Most of the examples have one table only. How I connect in case I have 2 tables (2nd table has left join)

Below is a typical example and working great:

   SELECT   *
    FROM OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI').Billing.dbo.Invoices

But I need to join invoices table with 'customer' table like below. I am not sure how I do that?? Please help

 SELECT *
FROM OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI').Billing.dbo.Invoices as inv
left join Billing.dbo.customers as cust
on inv.customer = cust.customer
- - Source

Answers

answered 1 week ago Conor Cunningham MSFT #1

OPENDATASOURCE is one way to talk to a remote server using the "linked server" or "distributed query" functionality in SQL Server. However, it is not likely the best path for you to use in this case as it does not allow for the SQL Server Query Optimizer to rewrite the query and push parts of the query down to the remote source (potentially reducing the number of rows returned to you over a slower network connection vs. your local database). If possible, creating an actual linked server would help you here. This would give you the option to say to the optimizer "these two tables are from the same remote source". Then the optimizer can consider plans that remotes a single query to the remote server that joins those two tables together, applies any filters and group by clauses, and then returns the result to the calling server.

Here's the mechanism to add a linked server: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-2017

Once you have a remote server (which I'll call "remote" here), you can write the query using the 4-part name syntax for remote servers instead of using OPENDATASOURCE.

SELECT * FROM REMOTE.Billing.DBO.Invoices LEFT JOIN REMOTE.Billing.DBO.Invoices on <join condition> <WHERE clause>

Here is a paper on how linked servers work under the covers which should give you a conceptual overview as to why this approach is likely better for you: http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.59.8007

Best of luck!

comments powered by Disqus