临时解决OPENDATASOURCE联合查询出的问题

跨服务器查询，我查到了如下的方法，opendatasource.

在执行跨服务器查询之前，运行

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

查询结束后，运行

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

用OPENDATASOURCE
下面是个跨SQLServer查询的示例
Select TableA.*,TableB.* From OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerA;User ID=UserID;Password=Password'
         ).databaseAName.dbo.TableA
Left Join
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerB;User ID=UserID;Password=Password'
         ).databaseBName.dbo.TableB On TableA.key=TableB.key

 

我按照这个写了一个一样的，但是不行？我写的是

select Tbl_Staff.*, VisitLog.* from  OPENDATASOURCE
  (
    'SQLOLEDB',
    'Data Source=server1;User ID=User;Password=UserPassword'
   ).[Attendance].dbo.Tbl_Staff
  
inner join 

  OPENDATASOURCE
(
  'SQLOLEDB',
  'Data Source=server2;User ID=User; Password=UserPassword
 ).[KB].dbo.VisitLog
 
 on [Attendance].dbo.Tbl_Staff.Mid=[KB].dbo.VisitLog.UID

 

结果服务器报错，如下

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Attendance.dbo.Tbl_Staff.Mid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "KB.dbo.VisitLog.UID" could not be bound.
Msg 107, Level 15, State 1, Line 1
The column prefix 'Tbl_Staff' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'VisitLog' does not match with a table name or alias name used in the query.

 

各位怎么办呢？我研究了很多都不行。

 

临时解决办法

后来我单独查询并保存于临时表，结果是可以的。如下

--查询保存到临时表A
select * into #A from  OPENDATASOURCE
  (
    'SQLOLEDB',
    'Data Source=server1;User ID=User;Password=UserPassword'

   ).[Attendance].dbo.Tbl_Staff
  
--查询保存到临时表B
select * into #B from  OPENDATASOURCE
(
  'SQLOLEDB',
  'Data Source=server2;User ID=User; Password=UserPassword

 ).[KB].dbo.VisitLog

 

这是为什么呢？