OPENDATASOURCE join syntax

As defined by BOL OPENDATASOURCE provides ad hoc connection information as part of a four-part object name without using a linked server name.

By the way, I am using SQL Server 2008 R2 and BOL from SQL Server 2008R2.

Great! This means you can set up a "on the fly" query to a remote server and database using ODBC, OLE DB or other connections without taking the time to set up linked servers etc.

Here is the example from BOL

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=London\Payroll;Integrated Security=SSPI')
.AdventureWorks2008R2.HumanResources.Employee

I was connecting to earlier versions of SQL (Both 2000 and 2005) for my distributed queries and used the SQLOLEDB provider instead.

So, here was my first OPENDATASOURCE query for this project.

select col1,col2,col3
from OPENDATASOURCE('SQLOLEDB','Data Source=server;User ID=user;Password=password)
.[dbname].schema.tablename
where condition1 = value1

This worked great. However my query was not complete. I needed to join to a different table. So I tried a few iterations of syntax for joining OPENDATASOURCE and didn't get it right. Google to the rescue! But I didn't find it there either.

After more trial and error I got it and since it was a pain in the neck to find I decided to post it here.

OPENDATASOURCE join syntax:

select
a.col1,a.col2,a.col3,
b.col1,b.col2,b.col3
from OPENDATASOURCE('SQLOLEDB','Data Source=server;User ID=user;Password=password)
.[dbname].schema.tablename1 a
join OPENDATASOURCE('SQLOLEDB','Data Source=server;User ID=user;Password=password)
.[dbname].schema.tablename2 b
where condition1 = a.value1
and condition2 = b.value2

This can go on forever too.

Happy coding!