How to make a query to join local database and linked server database

Question

I have created a linked server and give the name as test.

When I write a query like

select * 
from openquery(test, 'select * from account') 

it works fine but when I run this query:

select * 
from openquery(test, 'select * from account join test1 on account.accountid=test1.student_id') 

it throws an error

OLE DB provider "SQLNCLI10" for linked server "test" returned message "Deferred prepare could not be completed.".

I don't know what I am doing wrong to join both the server.

Answer 1

When any of the 2 server ( linked server and local server) is using ms sql 2012 and above. Then while creating the linked server make sure Under provider drop down we select SQL Server Native Client 11.0. The reason I was keep on getting the error is because under Provider I selected SQL Server Native Client 10.0 and my linked server is using sql2008 and my local server is sql2012

Solution

select * from test.account.dbo.account m  
    left join  
         test t  
            on m.accountid=t.student_id  

Answer 2

You can try as:

select * from test.master.dbo.account 
              join 
              test.master.dbo.test1 
                      on account.accountid=test1.student_id

likewise:

select * from test.master.dbo.account