-- SQL Server跨实例查询示例
--分类： 应用之SQL 2011-06-29 12:19 60人阅读 评论(0) 收藏 举报
--启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--查询示例(使用openrowset或opendatasource函数) 
select * from openrowset('SQLOLEDB' , 'sql服务器名' ; '用户名' ; '密码' , 数据库名.dbo.表名) 
select * from openrowset('SQLOLEDB' , '6f1ce7ba506f49b' ; 'sa' ; '168' , cw202.dbo.bmzl)

 

select a.* from opendatasource('sqloledb','server=服务器名或实例名;uid=sa;pwd=;database=库名').库名.dbo.table1 a
select a.* from opendatasource('sqloledb','server=6f1ce7ba506f49b;uid=sa;pwd=168;database=cw202').cw202.dbo.bmzl a


--使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
go




sp_addlinkedserver '10.53.6.170',N'SQL Server'
go
sp_helpserver
EXEC sp_addlinkedsrvlogin '10.53.6.170', 'false', NULL, 'sa', 'mobi@20120510'
select * from [10.53.6.170].ATE.dbo.ENA


sp_helptext sp_addlinkedsrvlogin



insert into [2011-20120227SE\SQLEXPRESS].[PMSMVC].dbo.DocTaskChange(TaskID,RecordTime,UserID,ChangeDetail) 
select TaskID,RecordTime,UserID,ChangeDetail from [10.53.6.170].PMS.dbo.DocTaskChange



insert into [2011-20120227SE\SQLEXPRESS].[PMSMVC].dbo.DocTask(ProjectID,TaskName,RecordTime,Publisher
,BeginTime ,PostTime,Executor,Checker,TaskDetail,TaskStatus) 
select ProjectID,TaskName,RecordTime,Publisher
,BeginTime ,PostTime,Executor,Checker,TaskDetail,TaskStatus from [10.53.6.170].PMS.dbo.DocTask









IF EXISTS (SELECT * FROM [10.53.6.41].[hrmis].sys.objects WHERE name = N'[10.53.6.41].[hrmis]' AND type in (N'V'))

exec sp_addlinkedserver '10.53.6.41', N'SQL Server'
exec sp_addlinkedsrvlogin '10.53.6.41','false ',null, 'hrview', 'hrview@mobi' 
go

select * from [10.53.6.41].[hrmis].[dbo].[View_a01]

-- 4 删除链接
exec SP_droplinkedsrvlogin '10.53.6.41','sa'   
exec sp_dropserver  '10.53.6.41 ', 'droplogins' 
go