首页 新闻 搜索 专区 学院

OPENDATASOURCE 不能跨服务器查询,始终报错

0
悬赏园豆:50 [已关闭问题] 关闭于 2013-06-07 01:13

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

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

 

这是为什么呢?

五月℃夏到了的主页 五月℃夏到了 | 初学一级 | 园豆:17
提问于:2013-05-29 13:25
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册