1, openquery 简单用法。
exec sp_addlinkedserver
@server='demo',--要连接的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='HNTV-DES'--要连接的服务器名称
exec sp_addlinkedsrvlogin
'demo',
FALSE,
NULL,
'sa',
'000000'
Select * From openquery([demo],'Select * From test.aa.tb2')
2,OPENDATASOURCE 简单用法
Select * From OPENDATASOURCE(
'SQLOLEDB','Data Source=192.168.0.11;Initial Catalog=Student;User Id=sa;Password=123456).Student.dbo.book
use master
go
exec sp_configure 'show advanced options',1
reconfigure
go
exec sp_configure 'Ad Hoc distributed Queries',1
reconfigure
go
Select * From OPENDATASOURCE(
'SQLOLEDB','Data Source=192.168.0.1;Initial Catalog=test;User Id=sa;Password=sa').test.dbo.tb1
go
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
go
exec sp_configure 'show advanced options',0
reconfigure
go