MS SQL SERVER2005跨服务器查询

1：打开'Ad Hoc Distributed Queries'

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

关闭只要将1设置为0

2：建立链路服务器
EXEC sp_addlinkedserver
'120Host', //昵称
'',
'SQLOLEDB',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=IP,1433;UID=sa;PWD=;'
go
exec sp_addlinkedsrvlogin '120Host','false','sa','sa',''
GO

3:既可以建立查询
select *
from openquery([120Host],'select * from 库名.dbo.表名')