select inner join between 2 linked server ?

Posted on 2010-03-25
Medium Priority
Last Modified: 2012-05-09

  Ok, here is a tricky one.

I have 2 linked server configured properly. I can execute select statement on both servers from Management Studio. So far so good.

But, for wathever reasons, the designers of the other system (which is a Pervasive DB) decided to split the tables between 2 distincts databases (the reason why I have 2 linked servers on 2 ODBC DSN). And I have to extract data on tables that are on both databases.

So I have those queries:
select * from openquery(ExactSQL, 'SELECT "Id Product" FROM Product where number=''PP141-72''')
select * from openquery(ExactGPAO, 'SELECT * FROM ProductRecipe where "Id Product" = 3532') => 3532 is the Id Product of PP141-72

Is there a way I can do a join between those tables?
Question by:Dominic34
LVL 32

Accepted Solution

Brendt Hess earned 2000 total points
ID: 28592729
This should work fine as:

select * from openquery(ExactSQL, 'SELECT "Id Product" FROM Product where number=''PP141-72''')
INNER JOIN  openquery(ExactGPAO, 'SELECT * FROM ProductRecipe where "Id Product" = 3532')
   ON .....

Author Comment

ID: 28594593
after some fine tuning, I could made it works:

select ExactSQL.[Id Product], ExactGPAO.* from openquery(ExactSQL, 'SELECT * FROM Product') as ExactSQL
INNER JOIN  openquery(ExactGPAO, 'SELECT * FROM ProductRecipe') as ExactGPAO
   ON ExactSQL.[Id Product] = ExactGPAO.[Id Product]
   where ExactSQL.number = 'PP141-72'


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question